Project 1 — Name Cleaning

Create:

Raw Name | Clean Name | Length

Use:

  • TRIM
  • PROPER
  • LEN

Project 2 — Employee Data Cleaning

Columns:

Employee ID | Employee Name | Department | Email

Extract:

  • Employee prefix
  • Year
  • Department code
  • Employee number

Use:

LEFT + MID + RIGHT + TRIM + PROPER + LOWER

Project 3 — Email Extraction

Email:

employee@sircltech.com

Create separate columns:

Email | Username | Domain

Example

Username:

=LEFT(A2,FIND(“@”,A2)-1)

Domain:

=RIGHT(A2,LEN(A2)-FIND(“@”,A2))

Project 4 — Data Formatting

Clean the raw data and create:

  • Proper Name
  • Lowercase Email
  • Standard Company Name
  • Formatted Date
  • Formatted Currency
  • Clean Employee ID

Practice File

Text_Functions_Workbook.xlsx

Create 4 worksheets:

  1. Name_Cleaning
  2. Employee_Data
  3. Email_Extraction
  4. Data_Formatting

Functions Covered

LEFT • RIGHT • MID • LEN • TRIM • UPPER • LOWER • PROPER • CONCAT • TEXTJOIN • FIND • SEARCH • SUBSTITUTE • REPLACE • TEXT • VALUE

PreviousNext