Cleaning and splitting text in Excel

Data rarely arrives clean. Names come in capitals, with double spaces. Numbers arrive as text. A code and a description share one cell. Before any totals or lookups can be trusted, the text has to be put right. This page is a set of recipes for that, using formulas that work in every recent version of Excel.

Sample sheet: Text as people really type it: stray spaces, mixed capitals, codes with dashes.

	A	B	C	D
1	Full name	Email	Code	Amount as text
2	asha verma	asha.verma@example.com	PB-10-1234	1,250
3	RAVI KUMAR	ravi@example.org	DL-08-0042	300
4	meena gill	meena.gill@example.com	MH-12-9876	45.50
5	Kabir Khan	kabir@example.net	RJ-14-0007	12 kg

Work in a spare column

Cleaning formulas do not change the original cell. They give their result somewhere else. The routine is always the same.

Write the formula in an empty column beside the data.
Copy it down.
When the results look right, copy them, and use Paste Special, Values over the original column.
Delete the helper column.

Keep a copy of the raw data on another sheet until you are sure.

1. Remove extra spaces

=TRIM(A2)
Result: asha verma
=LEN(A2)
Result: 15
=LEN(TRIM(A2))
Result: 10

TRIM removes spaces at both ends and squeezes runs of spaces between words down to one. The two lengths show how many characters went.

If TRIM appears to do nothing, the spaces are probably non-breaking spaces from a web page: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).

2. Fix the capitals

=PROPER(TRIM(A2))
Result: Asha Verma
=PROPER(A3)
Result: Ravi Kumar
=UPPER(C2)
Result: PB-10-1234
=LOWER(B2)
Result: asha.verma@example.com

PROPER gives each word a capital. Check its results on names with apostrophes and on abbreviations, which it gets wrong.

3. Split a full name

First name: everything before the first space. Surname: everything after it.

=LEFT(A3,FIND(" ",A3)-1)
Result: RAVI
=MID(A3,FIND(" ",A3)+1,100)
Result: KUMAR
=LEFT(TRIM(A2),FIND(" ",TRIM(A2))-1)
Result: asha

FIND locates the space, and LEFT and MID cut on either side of it. The third formula trims first, which is needed when the cell starts with spaces.

For a one-time job, two tools are quicker than formulas.

Flash Fill: type the first name for the first row in the next column, start typing the second, and press Ctrl and E. Excel copies the pattern.
Text to Columns on the Data tab splits a column at spaces, commas or any character you choose.

Names with three or more parts need a decision about what counts as the surname. The last word is =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",50)),50)), a well-known trick that pads every space out to fifty and takes the last fifty characters.

4. Split a code into its parts

=LEFT(C2,2)
Result: PB
=MID(C2,4,2)
Result: 10
=RIGHT(C2,4)
Result: 1234
=VALUE(RIGHT(C2,4))
Result: 1234

When every code has the same layout, the positions are fixed. The pieces are text. The last formula turns the serial into a number.

5. Split an email address

=LEFT(B2,FIND("@",B2)-1)
Result: asha.verma
=MID(B2,FIND("@",B2)+1,100)
Result: example.com

6. Join columns

=PROPER(A3)&" <"&B3&">"
Result: Ravi Kumar <ravi@example.org>
=TEXTJOIN(", ",TRUE,C2:C5)
Result: PB-10-1234, DL-08-0042, MH-12-9876, RJ-14-0007

The & sign joins pieces. TEXTJOIN joins a whole range with a separator and skips empty cells. See also CONCAT.

7. Remove or replace characters

=SUBSTITUTE(C2,"-","")
Result: PB101234
=SUBSTITUTE(C2,"-","/")
Result: PB/10/1234
=SUBSTITUTE(D5," kg","")
Result: 12

SUBSTITUTE swaps one piece of text for another wherever it occurs. Replacing with "" deletes.

8. Turn text into real numbers

Numbers stored as text will not add up.

=SUM(D2:D4)
Result: 0
=VALUE(D2)
Result: 1250
=VALUE(TRIM(D4))
Result: 45.5
=VALUE(SUBSTITUTE(D5," kg",""))
Result: 12
=SUMPRODUCT(VALUE(D2:D4))
Result: 1595.5

The SUM of the text column is zero. VALUE converts each entry, after any unit has been stripped. For a whole column at once, select it and use the warning sign's Convert to Number, or Data, Text to Columns, Finish.

9. Find what is wrong

Before cleaning, find out which rows need it.

=A2=TRIM(A2)
Result: FALSE
=ISNUMBER(D2)
Result: FALSE
=LEN(C2)=10
Result: TRUE
=ISNUMBER(SEARCH("example.com",B2))
Result: TRUE

The first is FALSE where a cell has stray spaces.
The second is FALSE where a "number" is really text.
The third checks that a code has the right length.
The fourth tests whether a cell contains a piece of text.

Put one in a helper column and filter for the rows that fail.

10. Find duplicates

After cleaning, repeated entries become visible, because "Asha Verma" and " asha verma" are now the same. =COUNTIF($A$2:$A$100,A2)>1 flags every value that occurs more than once. See COUNTIF. The Remove Duplicates command on the Data tab deletes the extra rows.

An all-in-one cleaning formula

For names from an untidy source:

=PROPER(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))))

Reading from the inside out: turn non-breaking spaces into ordinary ones, remove non-printing characters, trim the spaces, and fix the capitals.

A sensible order of work

Keep the original.
Trim spaces first. Many other problems disappear with them.
Fix the case.
Split or join columns.
Convert text numbers and text dates.
Check lengths and formats.
Look for duplicates.
Paste the results as values and remove the helper columns.

Things formulas should not do

Phone numbers, PIN codes and account numbers should stay as text. As numbers they lose leading zeros, and long ones lose digits after the fifteenth.
Names cannot be fully corrected by rule. Read the list after PROPER.
One-time clean-ups are often faster with Find and Replace (Ctrl and H), Flash Fill or Text to Columns than with formulas. Use formulas when the cleaning must be repeated on fresh data.

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.