Text functions: cleaning and combining words
Clean, split and join text with TRIM, PROPER, LEFT, MID, RIGHT, FIND, SUBSTITUTE and TEXTJOIN, plus Text to Columns and Flash Fill.
In this lesson you will
- Clean messy text with TRIM and PROPER
- Split text with FIND, LEFT and MID
- Join text with & and TEXTJOIN
- Clean safely into a new column
About 15 minutes
Words are data too
Zainab exports her customer list from an online shop and finds a mess: names typed in capitals, extra spaces, emails she needs to split into username and domain, and order codes where only the last four digits matter. Fixing a thousand rows by hand would take a day. Excel’s text functions clean and reshape words the way the number functions handle figures. They are the tools of everyone who works with imported data.
The toolbox
| Function | What it does |
|---|---|
| TRIM | Removes extra spaces, leaving single spaces between words |
| UPPER, LOWER, PROPER | Changes capitals |
| LEN | Counts characters |
| LEFT, RIGHT, MID | Takes characters from the start, end or middle |
| FIND | Gives the position of a character |
| SUBSTITUTE | Replaces text inside text |
| TEXTJOIN or & | Joins pieces of text together |
| VALUE | Turns number-looking text into a real number |
Real results
A test sheet held a messy name in A1 (with spaces at both ends and extra spaces in the middle), an email in A2 and a code, PEN-2026-0042, in A3. These results were really calculated:
| Formula | Result |
|---|---|
| =TRIM(A1) | riya SHARMA |
| = |
Riya Sharma |
| =LEN(A1) and =LEN(TRIM(A1)) | 16 and 11 |
| = |
riya.sharma |
| = |
example.com |
| =LEFT(A3,3) | PEN |
| =MID(A3,5,4) | 2026 |
| =RIGHT(A3,4) | 0042 |
| = |
43 |
| = |
987654 |
| =A4&” and “&A5 | Notebook and Pen |
| =TEXTJOIN(“, “,TRUE,A4:A5) | Notebook, Pen |
| = |
Not-007 |
Notice that the messy name had 16 characters before TRIM and 11 after. Those hidden spaces are why lookups and COUNTIFs fail on pasted data. Notice also that VALUE turned the last four characters of the code into a real number, which allowed adding 1.
Splitting with FIND
To take the username from an email, find the position of the @ with FIND, then take everything to the left of it, minus one: =
Joining
The ampersand glues pieces: =A4&” and “&A5 gave Notebook and Pen. TEXTJOIN joins a whole range with a separator, and its second argument TRUE skips blanks: it gave Notebook, Pen. TEXTJOIN is available in Excel 2019 and later and in Google Sheets; older versions use the ampersand. To make a code such as Not-007, take three letters from a name and join a number formatted to three digits with TEXT.
Cleaning a column step by step
- Insert a new column beside the messy one. Never overwrite the original.
- Write the cleaning formula in the first row, for example =
PROPER( .TRIM( A2)) - Copy it down the column.
- Check a few rows and the last row.
- Copy the cleaned column, then choose Paste Special, Values over itself (or into a new place) to turn formulas into fixed text.
- Delete the original column only when you are sure.
No-formula helpers
- Text to Columns (Data tab) splits one column into many at commas, spaces or any character.
- Flash Fill (Ctrl+E): type the result you want for the first one or two rows, then press Ctrl+E and Excel guesses the pattern for the rest. Check its work, because it can guess wrongly.
- Find and Replace (Ctrl+H) fixes typos and removes characters in bulk.
Dangers
- TRIM does not remove non-breaking spaces from web pages. Use SUBSTITUTE(
A1, for those.CHAR( 160), ” “) - Numbers made with text functions are text until you use VALUE. A left-aligned result is the clue.
- LEFT, MID and RIGHT give text, even when they return digits.
- Do not clean the only copy of your data. Keep the raw column.
Practice
Make a column of ten messy names, with capitals, extra spaces and one with a trailing space. Create a clean column with PROPER and TRIM. Add ten emails and split them into username and domain. Then build product IDs by joining the first three letters of a product name, a hyphen and a three-digit number.
A product ID formula could be =
Overwriting the original data with formulas. Always clean into a new column, keep the raw data until the end, and paste values only after checking.
A worked example: tidy a customer list
Suppose column A holds full names such as ‘riya SHARMA’ and column B holds emails. In column C use =
Names are tricky in real life: some people have one name, some have three, and capitalisation such as McDonald or de Souza may not follow PROPER’s rules. Check unusual rows by eye and fix them by hand rather than trusting the formula blindly.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
Which function removes extra spaces?
- LEN
- MID
- TRIM
- ROUND
It keeps single spaces between words.
How do you take the text before the @ in an email?
- RIGHT only
- SUM
- VALUE
- LEFT with FIND minus one
Find the marker, then cut to the left of it.
What does VALUE do?
- Adds a value
- Turns number-looking text into a number
- Counts spaces
- Changes the font
Text results are not numbers until converted.
Which shortcut starts Flash Fill?
- Ctrl+F
- Ctrl+Z
- Ctrl+E
- Ctrl+P
Always check its guesses.
Where should cleaning formulas be written?
- In a new column
- Over the original data
- In the header
- In the file name
Keep the raw data safe.
Summary
- Text functions clean and reshape imported data.
- Split by finding a marker and cutting before or after it.
- VALUE turns number-looking text into numbers.
- Clean into a new column; keep the raw data.
Questions
What is the difference between TRIM and CLEAN?
TRIM removes extra spaces. CLEAN removes non-printing characters. Use both on text pasted from other programs.
Why does Flash Fill not work?
It needs a clear pattern in the first rows and an empty column next to the data. Type one or two examples first.
Can I split text without formulas?
Yes. Use Data, Text to Columns, or Flash Fill.
