Ravindra BagaleCourses & study guides Track your progress

Guides

Excel Text Functions for Data Cleanup: TRIM, PROPER, LEFT, MID, FIND

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Zoya downloads a customer list. One name is " ruhi bagale " with spaces at both ends, one is AMIR in capitals, and one is shraddha bagale with three spaces in the middle. The order codes look like PUN-1023-A: city code, number and grade in one cell. Text functions fix all of this without retyping.

Text functions take text in and give cleaner text out. TRIM removes extra spaces, PROPER/UPPER/LOWER change the case, and LEFT, MID, RIGHT, FIND, LEN and SUBSTITUTE cut and change parts of the text.

Download the practice file (text-functions-data-cleanup.xlsx)

Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. Insert a new column next to the messy one.
  2. Type =PROPER(TRIM(A2)) and press Enter.
  3. Double-click the fill handle to copy it down.
  4. Copy the new column, then Home > Paste > Paste Values over it to keep only the clean text.
  5. Delete the old column when you are happy.
=PROPER(TRIM(A2))
=LEFT(C2,FIND("-",C2)-1)

Clean the names

Raw name (A)FormulaResult
ruhi bagale =PROPER(TRIM(A2))Ruhi Bagale
AMIR=PROPER(TRIM(A3))Amir
shraddha bagale=PROPER(TRIM(A4))Shraddha Bagale

TRIM removes spaces at the start and end and turns several spaces in the middle into one. PROPER makes the first letter of each word a capital. Use UPPER for codes and LOWER for e-mail addresses.

Cut a code into parts

Code in C2: PUN-1023-A.

You wantFormulaResult
First 3 letters (city code)=LEFT(C2,3)PUN
4 characters starting at position 5=MID(C2,5,4)1023
Last character (grade)=RIGHT(C2,1)A
Length of the code=LEN(C2)10
Position of the first dash=FIND("-",C2)4
Text before the first dash (any length)=LEFT(C2,FIND("-",C2)-1)PUN
Number part as a real number=VALUE(MID(C2,5,4))1023
Code without dashes=SUBSTITUTE(C2,"-","")PUN1023A

LEFT, MID and RIGHT always return text. Wrap them in VALUE when you need a number you can add or sort numerically.

Clean phone numbers

=SUBSTITUTE(D2," ","") turns 98220 12345 into 9822012345 (fictional numbers). To be sure the result has 10 digits: =LEN(SUBSTITUTE(D2," ",""))=10 gives TRUE.

Which function when

ProblemFunction
Extra spacesTRIM (and CLEAN for odd line-break characters)
Wrong casePROPER, UPPER, LOWER
Fixed-width partsLEFT, MID, RIGHT
Parts split by a dash or spaceFIND + LEFT/MID, or TEXTBEFORE / TEXTAFTER in Microsoft 365
Replace a character everywhereSUBSTITUTE
Join parts back& or TEXTJOIN

No formulas wanted? Try Data > Flash Fill (Ctrl + E) or Data > Text to Columns for one-time splits.

Ravindra Bagale's Tip

After cleaning, always Paste Values over the formula column before you delete the raw column. Otherwise every clean cell turns into #REF! the moment the raw data disappears.

Practice task

  1. Open the Text sheet. Check B2:B4 show Ruhi Bagale, Amir and Shraddha Bagale.
  2. In F2 type =LEFT(C2,3) and copy down. Expect PUN, NSK, NGP.
  3. In G2 type =MID(C2,5,4). Expect 1023 for row 2.
  4. Use =SUBSTITUTE(D2," ","") to remove the space from the phone numbers.
  5. Check the answers in the yellow-green cells on the right.

Learn it properly

Course lessons:

Related guides: CONCAT and TEXTJOIN · Remove Duplicates

Frequently asked questions

Why does TRIM not remove some spaces?

Web data often has non-breaking spaces (character 160). Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

Why can I not add the number from MID?

MID returns text. Wrap it: =VALUE(MID(C2,5,4)) or add zero.

FIND or SEARCH?

FIND is case-sensitive and does not allow wildcards; SEARCH ignores case and allows * and ?.

Does PROPER work for names like McDonald?

No, it gives Mcdonald. Fix such exceptions by hand.