Text and date functions

Data Preparation in Excel

Iason Prassides

Content Developer, DataCamp

Data types

  • Text functions
    • Deal with text strings, e.g., name

Example of a manually entered table of student test data within Excel with the text sting column full_name highlighted

Data Preparation in Excel

Data types

  • Text functions
    • Deal with text strings, e.g., name
  • Data & Time functions
    • Deal with date & time information, e.g., test_date

Example of a manually entered table of student test data within Excel with the date column test_date highlighted

Data Preparation in Excel

Text functions - LEN()

LEN()

  • Syntax: LEN(text)
  • Counts the number of characters in a text string
    • Including spaces and special characters

Business case

  • Identify anomalies
  • Ensure phone numbers stored have been entered correctly

Image showing a table in excel that has phone numbers in one column, and the use of the LEN formula to count and check the length of phone number values inputed

Data Preparation in Excel

Text functions - CONCAT()

CONCAT()

  • Syntax: CONCAT(text1, [text2],...)

  • Combines the text from multiple ranges and/or strings

  • Delimiters have to be entered manually

Business case

  • Create new categories and reduce columns
  • Combine delivery address information

Image of a table in Excel showing the street and city in separate columns, with a third column using the CONCAT function to combine the information.

Data Preparation in Excel

Text functions - TEXTJOIN()

TEXTJOIN()

  • Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2],...)
  • Combines text from multiple ranges and/or strings, and includes a delimiter
  • Can include/exclude empty cells

Business case

  • Create URL address

 

Image of a table in Excel that has columns for the the URL start, name, and domain in different rows.

Data Preparation in Excel

Date and time functions - TODAY()

TODAY()

  • Syntax: TODAY()
  • Returns the serial number of the current date
  • The serial number is the date-time code used by Excel

Business case

  • Inform users of last update of table

Image of a table in Excel, with the date of the last time the table data was updated using the TODAY function

Data Preparation in Excel

Date and time functions - WEEKDAY()

WEEKDAY()

  • Syntax: WEEKDAY(serial_number,[return_type])
  • Returns the day of the week corresponding to a date, as an integer
    • Default: 1 (Sunday) to 7 (Saturday)

Business case

  • Calculating day of the week using sales date information for analysis

Image of a table in Excel that is displaying a stock item and its delivery date. A new column indicating the weekday has been added by using the WEEKDAY function.

Data Preparation in Excel

Date and time functions - WORKDAY()

WORKDAY()

  • Syntax: WORKDAY(start_date, days, [holidays])
  • Returns a number that represents a date that is the number of working days before or after a starting date.
  • Excludes weekends and holidays

Business case

  • Calculate employee availability based on time to complete current project

Image of an Excel table displaying employee names and current project start dates. Using an average days per project, a new column has been added using WORKDAY function to show the next available date for each employee

Data Preparation in Excel

Protection and security

Various options to protect your data:

  • File-level
  • Workbook-level
  • Worksheet-level
Data Preparation in Excel

Protecting the worksheet

  • Controls how users can work within a sheet
  • Enabling whether users can:
    • Add or remove rows and columns
    • Sort data
    • AutoFilter data
  • Includes cells, ranges, and formulas

Protecting the sheet pop up

Data Preparation in Excel

Let's practice!

Data Preparation in Excel

Preparing Video For Download...