Introduction
Much of the data you receive is text that needs work: names in capitals, codes that combine a region and a number, extra spaces from a copy and paste, dates written as words. Text functions take strings apart, clean them and put them back together, so that the data can be sorted, looked up and counted correctly.
How it works
Taking parts. =LEFT(A2,3) returns the first three characters, =RIGHT(A2,4) the last four, and =MID(A2,5,2) two characters starting at position 5. =LEN(A2) counts the characters, spaces included.
Finding positions. =FIND("-",A2) returns the position of the first hyphen in A2. FIND is case-sensitive; SEARCH is not and allows the wildcards * and ?. Both return #VALUE! when the text is not found. Combined with LEFT and MID they split text at a separator: =LEFT(A2,FIND("-",A2)-1) returns everything before the first hyphen.
Cleaning. =TRIM(A2) removes spaces at both ends and turns runs of spaces inside into single spaces. It does not remove the non-breaking spaces that web pages contain; =SUBSTITUTE(A2,CHAR(160)," ") turns them into ordinary spaces first. CLEAN removes non-printing characters such as line breaks from other systems. UPPER, LOWER and PROPER change case; PROPER capitalises the first letter of each word.
Replacing. =SUBSTITUTE(A2,"Ltd","Limited") replaces every occurrence of a text, and an optional fourth argument replaces only the nth one.
Joining. The & operator joins text: =B2&", "&A2. CONCAT joins a list or a range, and TEXTJOIN, available in Excel 2019 and later, joins with a separator and can skip empty cells: =TEXTJOIN(", ",TRUE,A2:A6). In Microsoft 365, TEXTSPLIT does the reverse, spilling the parts of a string into separate cells.
Numbers and text. =TEXT(B2,"0.00") or =TEXT(C2,"dd mmm yyyy") turn a number or date into formatted text, which is how you put a nicely formatted date inside a sentence: ="Due on "&TEXT(C2,"dd mmm yyyy"). The result is text, so it cannot be used in further arithmetic. =VALUE("125") turns number-like text back into a number.
Example
Product codes combine a region and a number, with messy spacing:
| A | B | C | |
|---|---|---|---|
| 1 | Code | Region | Number |
| 2 | NOR-1042 | ||
| 3 | sou-77 |
Line by line
- In D2 type
=TRIM(A2)and fill down, giving clean codes. - In B2 type
=UPPER(LEFT(D2,FIND("-",D2)-1))and fill down: the part before the hyphen, in capitals. - In C2 type
=VALUE(MID(D2,FIND("-",D2)+1,LEN(D2)))and fill down: the part after the hyphen, as a number. - In E2 type
=B2&" #"&C2for a display label.
Expected result
D2 and D3 show NOR-1042 and sou-77. B2 and B3 show NOR and SOU. C2 and C3 show the numbers 1042 and 77, aligned right because they are numbers. E2 shows NOR #1042 and E3 shows SOU #77.
In the real world
Data from websites, forms and older systems almost always needs this kind of cleaning: splitting full names, extracting postcodes, standardising case so that lookups match, and building labels or email addresses from parts. Power Query, covered later, does the same work at a larger scale, but text functions are the quick way for one-off fixes.
Common mistakes
- Lookups failing because of hidden spaces; TRIM both sides first.
- Using FIND when the case varies; use SEARCH.
- Doing arithmetic on the result of TEXT, which is text.
- Assuming TRIM removes every kind of space; non-breaking spaces need SUBSTITUTE with CHAR(160).
Best practices
- Clean in helper columns, check the result, then paste values over the original if needed.
- Split at a separator found with FIND or SEARCH rather than at a fixed position.
- Keep numbers as numbers; use TEXT only for display inside text.
Keyboard shortcuts
| Action | Windows | Mac |
|---|---|---|
| Flash Fill (split or join by example) | Ctrl+E | — |
| Find | Ctrl+F | Command+F |
| Replace | Ctrl+H | Control+H |
| Paste Special, to paste values | Ctrl+Alt+V | Control+Command+V |
| Fill a formula down | Ctrl+D | Command+D |
Summary
LEFT, RIGHT and MID cut text, with FIND or SEARCH locating separators and LEN measuring length. TRIM, CLEAN, SUBSTITUTE and the case functions clean text; &, CONCAT and TEXTJOIN join it; TEXT formats numbers as text and VALUE turns text back into numbers.
Quick check
Key takeaways
- LEFT, RIGHT and MID cut text; FIND and SEARCH say where to cut.
- TRIM, CLEAN and SUBSTITUTE clean messy imported text.
- TEXT turns a number into formatted text; VALUE turns number-like text into a number.
My notes
Write down what you want to remember from this lesson. Notes are saved on this device as you type, and appear on your dashboard.