Day 6 — FIND, SUBSTITUTE and splitting text

Splitting a column when you do not know where the split point is.

FIND — where is that character?

=FIND(what to find, inside what)

This lesson uses the Raw sheet below — deliberately messy, the way exported data really arrives.

FIND returns the position number. The dash in DL-2024-0912 is at position 3. The optional fourth argument says where to start looking, which is how you find the second dash.

FIND is case-sensitive, SEARCH is not

SEARCH finds it at position 8. FIND returns #VALUE! because the text is lower case and FIND cares. When you do not care about case — which is most of the time — use SEARCH.

Splitting a full name

This is the classic. The first name is everything before the first space, so: find the space, take everything to its left.

The -1 matters: without it you take the space too. Note that TRIM appears in both places — if you find the space in the trimmed text but cut from the untrimmed text, the positions do not line up and you get nonsense.

And the surname

+1 starts just after the space, and 50 is simply "more characters than any name will have" — MID stops at the end of the text rather than complaining.

Why not just use Text to Columns? Text to Columns is faster for a one-off. But it is a manual step someone has to remember every month, and it overwrites the original. A formula updates itself when the data refreshes. For a recurring MIS, use the formula.

Pulling the username out of an email

Exactly the same pattern as the name split. Once you have seen it once, you have seen it everywhere: find the separator, cut on either side of it.

SUBSTITUTE — replace text

=SUBSTITUTE(text, old, new, [which occurrence])

Leave the replacement empty to delete something. Add a fourth argument to change only one occurrence — the third line changes the second dash and leaves the first alone.

SUBSTITUTE matches text, REPLACE matches position Use SUBSTITUTE when you know what to change. Use REPLACE when you know where it is.

Counting occurrences with SUBSTITUTE

A neat trick: delete the character, see how much shorter the text got, and that is how many there were. The second line counts words by counting spaces and adding one.

CONCAT and TEXTJOIN

TEXTJOIN puts a separator between every item, which saves typing &", "& over and over. The second argument, TRUE, means "skip blanks" — the last line gives a, b rather than a, , b.

Try these yourself

Find the position of the @ sign in C4.
Extract the first name from A5 and capitalise it properly.
Extract the surname from A3.
Turn the code in B3 into one with no dashes at all.
Join the city and code from row 2 into New Delhi / DL-2024-0912.

← PreviousDay 5 — Cleaning text: TRIM, LEFT, RIGHT, MID Next →Day 7 — Working with dates