You receive customer data exported from different systems. Some names have extra spaces, some cities are in all caps, and customer codes contain useful city and ID information. Text Functions help you clean and reshape the data before reporting.

Clean text with TRIM and PROPER

TRIM removes unnecessary extra spaces. PROPER capitalizes each word. These are often the first formulas you should use on messy imported data.

Before and after cleaning

Raw ValueFormulaClean Output
aarav traders =PROPER(TRIM(A2))Aarav Traders
AHMEDABAD =PROPER(TRIM(A2))Ahmedabad
Simple rule: Clean spaces and text case before doing lookups, grouping or reporting.

Extract text with LEFT, RIGHT and MID

When codes follow a consistent pattern, you can extract useful parts from them. For example, from CUST-AHD-1001, you can extract AHD as city code and 1001 as customer number.

=TRIM(A2)   =MID(A2,6,3)   =RIGHT(A2,4)
LEFTStart of text
MIDMiddle of text
RIGHTEnd of text

Join text and extract email domain

You can join text using the & operator. Example: =B2&" - "&C2 creates a display name like Aarav Traders - Ahmedabad.

You can also combine RIGHT, LEN and FIND to extract the email domain after the @ symbol.

Join text=CustomerName&" - "&City
Extract domain=RIGHT(Email,LEN(Email)-FIND("@",Email))
Standardize case=PROPER(TRIM(City))
Extract ID=RIGHT(CustomerCode,4)
💡
Professional habit: Keep raw data unchanged and create cleaned columns separately. This makes your work easier to audit.
⚠️
Common mistake: Extracting text based on fixed positions when the code pattern is inconsistent. First check whether all codes follow the same structure.

Interview question

Question: Which text functions are most useful for cleaning imported Excel data?

Answer: TRIM for extra spaces, PROPER or UPPER for text case, LEFT/RIGHT/MID for extracting code parts, LEN for length checks, FIND for locating characters, and & or TEXTJOIN for combining text.

🧪 Practice Lab

Clean customer data using Text Functions

Download one ZIP file containing the practice workbook, challenge workbook, solution workbook, formula cheat sheet, quiz and answer key.

📦 Download Lesson 21 ResourcesWorks as a standalone file and after website deployment

Mini assignment

  1. Use TRIM to clean Customer Code.
  2. Use PROPER and TRIM to clean Customer Name and City.
  3. Use MID to extract City Code.
  4. Use RIGHT to extract Customer Number and Order Number.
  5. Use FIND and RIGHT to extract Email Domain.
  6. Use & to create Display Name.

Quick quiz

  1. Which function removes extra spaces?
  2. Which function capitalizes each word?
  3. Which function extracts text from the middle?
  4. How do you join two text values?

Answers: TRIM; PROPER; MID; use & or TEXTJOIN.

FAQ

Yes. Extra spaces and inconsistent case can cause lookup and reporting issues.
For small cleaning tasks, yes. For repeatable large cleaning workflows, Power Query is usually better.