Concatenate in Excel, then clean it up: &, TRIM and PROPER

Joining text is the most searched text job in Excel, and exported data almost always needs cleaning after. This lesson does both: join first and last names, strip the stray spaces, fix the capitals, and split the domain off an email address. The sheet above is the lesson; this page explains it, with an example worked out by the same formula engine.

Joining text with &

The & sign glues two pieces of text into one. Text you type into the formula goes in double quotes, so a space between two names is " ": =A2&" "&B2.

CONCAT does the same as a function, =CONCAT(A2," ",B2), and the older CONCATENATE still works. TEXTJOIN puts a separator between every piece for you and can skip empty cells, which helps with long lists: =TEXTJOIN(", ",TRUE,A2:A9). CONCAT and TEXTJOIN need Excel 2019 or later.

A worked example

This sheet comes from the lesson: names as they often arrive from another system, with spaces before and after and capitals all over the place.

In D2, =A2&" "&B2 gives femke JACOBS . The join works, and it faithfully keeps every stray space and capital. Filled down, every row is joined the same way.

TRIM and PROPER

TRIM

Removes spaces at the start and end, and turns any run of spaces between words into one.

PROPER

Gives every word a capital first letter and makes the rest small. UPPER and LOWER make everything one or the other.

One goes inside the other: =PROPER(TRIM(D2)) gives Femke Jacobs. Excel works from the inside out, so TRIM cleans the spaces and PROPER fixes the capitals of what TRIM gives it. The order of the two doesn't matter here; both have to be there.

With only one of them the result is half clean. =TRIM(D2) gives femke JACOBS: spaces gone, capitals still wrong. The lesson names both half-way answers, and it compares capitals exactly on this step, since capitals are the point.

Splitting text: TEXTAFTER and TEXTBEFORE

=TEXTAFTER(text, delimiter) returns everything after the first time the delimiter appears. For the domain of an email, the delimiter is "@": =TEXTAFTER(C2,"@") gives example.net. TEXTBEFORE gives the part before it: =TEXTBEFORE(C2,"@") gives femke.jacobs.

TEXTAFTER and TEXTBEFORE are in Microsoft 365 and Excel 2024. In older versions, find the position of the @ and take what comes after it: =MID(C2,FIND("@",C2)+1,100) gives example.net. The 100 only has to be longer than any domain.

The lesson, step by step

1. 1 Join first and last name

   In D2: =A2&" "&B2 gives femke JACOBS .

2. 2 Join them all

   Fill D2 down over D2:D7. The last one, D7, becomes =A7&" "&B7.

   They give bram VOS RUBEN KOK nora peters lena hendriks sara Dekker

3. 3 Clean it up

   In E2: =PROPER(TRIM(D2)) gives Femke Jacobs.

   Named mistake: =TRIM(D2) gives femke JACOBS. Spaces gone. Now wrap it in PROPER to fix the capitals.

   Named mistake: =PROPER(D2) gives Femke Jacobs . Capitals fixed, but the extra spaces are still there. Wrap it in TRIM too.

4. 4 Clean them all

   Fill E2 down over E2:E7. The last one, E7, becomes =PROPER(TRIM(D7)).

   They give Bram Vos Ruben Kok Nora Peters Lena Hendriks Sara Dekker

5. 5 Split off the domain

   In F2: =TEXTAFTER(C2,"@") gives example.net.

6. 6 Split them all

   Fill F2 down over F2:F7. The last one, F7, becomes =TEXTAFTER(C7,"@").

   They give example.net example.com example.org example.com example.net

In real Excel

PROPER capitalises after anything that isn't a letter, so it gets names like o'neil right (O'Neil) and names like mcdonald wrong (Mcdonald); check the result on real names. TRIM only removes the ordinary space. Data copied from a web page often holds non-breaking spaces, which it leaves alone: =TRIM(SUBSTITUTE(A2,UNICHAR(160)," ")) turns them into ordinary spaces first. Once a column is clean, copy it and paste it back as values if you want to delete the messy original.

Keep going

1. 9
   Next up is Lists: remove duplicatesKeep each value once with UNIQUE, then filter and sort, all with formulas.
2. 9
   Lists: remove duplicatesKeep each value once with UNIQUE, then filter and sort, all with formulas.
3. 7
   Lookups: VLOOKUPFind a code in a table and bring back its row, with VLOOKUP and XLOOKUP.