Text functions: LEFT, MID, TRIM, CONCAT and more

The idea

The café now has a loyalty programme, and the marketing team wants to send customers a personal message: "Hi Ayu, thanks for visiting our Jakarta branch!" The customer list comes out of the till system as an export, and when you open it, it's a mess. Some names are in all capitals, some in all small letters, and one has extra spaces in front. Each customer code squeezes the branch and the customer number into one cell. The phone numbers are written with dashes in some rows and spaces in others, and the messaging tool only accepts plain digits.

You could fix all of this by hand, and for five customers that might even be quicker. But the real list has thousands of rows, and a fresh export arrives every week, just as messy as the last one. Text functions let you write the clean-up once, as formulas. Next week you paste the new export in, and the clean columns fill themselves.

This module's sheet is a small piece of that export:

Cutting text: LEFT, RIGHT, MID, LEN

Start with the customer codes. Each one is made of three parts that are always in the same place: a 3-letter branch, a dash, and a 4-digit customer number. JKT-0012 means customer 12 of the Jakarta branch. To use those parts separately, for example to count customers per branch, you need to cut the code apart. These four functions do the cutting:

# The branch code: the first 3 characters
F2: =LEFT(A2, 3)
# The customer number: the last 4 characters
G2: =RIGHT(A2, 4)
# 4 characters starting at position 5
H2: =MID(A2, 5, 4)
# How many characters the raw name has, spaces included
I2: =LEN(B2)
# How many characters "Kopi Susu" has
I3: =LEN("Kopi Susu")

F2: JKT
G2: 0012
H2: 0012
I2: 14
I3: 9

LEFT takes the first 3 characters, JKT, and RIGHT takes the last 4, 0012. MID gets the same number from the middle: start at character 5 (just after the dash) and take 4. For a fixed layout like this, RIGHT is simpler, but MID is what you'd use if the part you wanted were stuck in the middle.

LEN counts characters, and it reveals a hidden problem. LEN(B2) is 14, even though "ayu lestari" only has 11 characters. The difference is the stray spaces before and after the name, which you can't see but which are definitely there. They're exactly the kind of thing that makes a name fail to match later, so it's good to know how to spot them.

One more detail about the customer number. Anything a text function gives you back is text, even when it looks like a number. 0012 from RIGHT is the text "0012", not the number twelve, so Excel won't sort it or add it up the way you'd expect. VALUE turns digits back into a real number:

# Turn the text "0012" into the number 12
J2: =VALUE(RIGHT(A2, 4))

J2: 12

Cleaning: TRIM, PROPER, UPPER, LOWER

Now the names. They have two separate problems: extra spaces, and messy capitals. TRIM deals with the first and PROPER with the second. Because a function's answer can be given straight to another function, you can do both in one formula: TRIM cleans up the spaces first, and PROPER fixes the capitals of what TRIM gives back:

F2:F6: =PROPER(TRIM(B2))
# The city in capitals
G2: =UPPER(C2)
# The code in lower case
G3: =LOWER(A3)

F2: Ayu Lestari
F3: Budi Santoso
F4: Citra Dewi
F5: Dewi Anggraini
F6: Eko Prasetyo
G2: JAKARTA
G3: bdg-0045

Replacing: SUBSTITUTE

Every name now looks right, including Citra's, which had two spaces between her first and last name. TRIM turns any run of spaces inside the text into a single one.

Next, the phone numbers. The messaging tool wants plain digits, so the dashes and spaces have to go. SUBSTITUTE(text, old, new) finds every occurrence of old in the text and replaces it with new. If you replace something with "", which is empty text, you're effectively deleting it. Some numbers use dashes and some use spaces, so to be safe you remove both. That takes two SUBSTITUTEs, one inside the other: the inner one removes the dashes, and the outer one removes the spaces from the result:

# Remove the dashes from the phone number
F2: =SUBSTITUTE(D2, "-", "")
F3:F4: =SUBSTITUTE(SUBSTITUTE(D3, "-", ""), " ", "")

F2: 081234567890
F3: 081322221111
F4: 085712345678

A fourth argument replaces only the n-th occurrence: SUBSTITUTE(D2, "-", ".", 1) swaps only the first dash.

Pieces of different lengths: FIND and SEARCH

For the personal message you need just the first name: "Hi Dewi", not "Hi Dewi Anggraini". The customer codes were easy to cut because every code has the same layout. Names don't: "Dewi" has four letters and "Eko" has three, so you can't just tell LEFT to take 4 characters.

What every name does have is a space between the first and last name. FIND(find_text, text) tells you the position where something first appears, so you can find the space and cut just before it. That way the formula works it out for each name instead of you counting by hand:

# The position of the space in Dewi's clean name
P2: =FIND(" ", F5)
# The first name: everything before the space
P3: =LEFT(F5, FIND(" ", F5) - 1)
# The last name: everything after the space
P4: =MID(F5, FIND(" ", F5) + 1, 100)

P2: 5
P3: Dewi
P4: Anggraini

The space in "Dewi Anggraini" is character number 5. So the first name is the first 4 characters, which is why P3 takes FIND(...) - 1: one less than the position of the space. The last name starts just after the space, at FIND(...) + 1. For the length, MID is asked for 100 characters, which is a common trick for "the rest of the text". There aren't 100 characters left, so MID simply stops at the end.

SEARCH works like FIND but ignores upper and lower case and understands the wildcards * and ?. If the text isn't there, both return #VALUE!, which you can catch with IFERROR (module 6).

Joining: &, CONCAT, TEXTJOIN

Finally, the clean pieces have to be put back together into a message. The & symbol glues pieces of text together, one after another. Anything you want in between, like a space or a bracket, you add as text in quotes:

# The clean name with the city in brackets
N2: =PROPER(TRIM(B2)) & " (" & PROPER(C2) & ")"
# Join the branch and number with a slash
N3: =CONCAT(LEFT(A3, 3), "/", RIGHT(A3, 4))

N2: Ayu Lestari (Jakarta)
N3: BDG/0045

N2 trims and capitalises the name, then adds " (", the city in proper capitals, and ")". CONCAT does the same job as &, but you list the pieces as arguments separated by commas, and it can also take a whole range.

When you're joining a list of things, TEXTJOIN(delimiter, ignore_empty, text...) is the easiest. You give it the separator once, and it puts it between the items, so you don't end up with a stray comma at the end. With TRUE as its second argument, it also skips empty cells:

# Three codes in one cell, separated by commas
O2: =TEXTJOIN(", ", TRUE, A2:A4)

O2: JKT-0012, BDG-0045, SBY-0103

Why formulas instead of fixing by hand?

Back to the beginning: for five customers, retyping the names by hand would honestly be faster. But you can't retype 5,000, and you certainly don't want to do it again next week when the fresh export comes in. The usual setup is to keep the raw export untouched in its own columns, and put the cleaning formulas in helper columns next to it. When the new export arrives, you paste it over the old raw data and the clean columns rebuild themselves.

Sometimes you need the clean result as plain text, without formulas, for example to send the list to the marketing team. Copy the clean column and use Paste Special → Values, which pastes what the formulas show instead of the formulas themselves.

Recap

• LEFT, RIGHT, MID cut out pieces; LEN counts characters; VALUE turns digit text into a number.
• TRIM removes extra spaces; PROPER, UPPER, LOWER fix capitals.
• SUBSTITUTE(text, old, new) replaces (or, with "", deletes) text; nest it for several replacements.
• & and CONCAT join text; TEXTJOIN joins a range with a delimiter and can skip empty cells.
• Keep cleaning formulas next to the raw data so they rebuild with every new export.