3 Winning Excel Text Functions!

#1: Use TRIM to remove whitespaces in text.

In the example below, ”   James  Johnson  ” has leading and trailing white spaces. TRIM removes the leading and trailing spaces and converts the middle set of whitespace into just one whitespace. It does a great job of cleaning up extra whitespaces that may come from data you’ve imported.

#2: Use PROPER to make first names/last names look better.

Proper capitalizes the first letter in a text string and makes the rest lower case. In the examples below, PROPER takes the lower case “james” and makes it “James”. It fixes “jOHNSON” to “Johnson” and capitalizes the L in lane and H in highway. PROPER takes that text and neatens it up! Similarly, you can use UPPER & LOWER to make strings uppercase and lowercase.

#3: Use CONCAT to join text together.

CONCAT is great to join text together – just pass in the cells, or text you want to join together as arguments to the CONCAT function. In the example below we want to combine Neil Armstrong’s name, but also add “Astronaut” in front and add his middle name Alden. Concat takes care of this for us – we just pass in the cells & the text as parameters and it joins it correctly. Note – I have a space before and after
Alden” – if I didn’t, there would be no space on the final word! You can also join text in a simpler fashion by typing in cell names, or text names together with “&”. For e.g. you could create the text below by typing in =”Astronaut “&B2&” Alden “&C2