Course / Clean & Check Data

Text surgery with nested formulas

Cut out the text between brackets, count characters, find the last word and draw in-cell bar charts. Advanced · 6 min · MID + FIND, Counting characters, Last word, REPT

You can already cut text at a fixed position, or at a single character. Real messages are messier: the part you want sits somewhere in the middle, or right at the end, and its length changes from row to row. The trick is to nest functions, so that one works out a position or a count and the next one uses it.

This export from a shop's shared inbox has four jobs waiting:

• pull each order reference out from between its square brackets
• count how many tags each message has
• find each customer's surname, the last word of their name
• turn the satisfaction score into a little bar chart

Build each formula from the inside out, and check each piece as you go.

Steps

1. Order references come in different lengths, but they always sit between [ and ]. In E2:E9, cut each one out with MID, using FIND to locate both brackets.

2. How many tags does each message have? Count the commas and add 1. In F2:F9, compare the length of the tags with their length once SUBSTITUTE has deleted every comma.

3. Surnames are the last word of each name, but some customers have middle names, so there's no single space to split at. In G2:G9, use the padding trick: SUBSTITUTE swaps every space for 50 spaces, RIGHT takes the last 50 characters, and TRIM strips off the padding.

4. REPT has a sunnier use too. In H2:H9, draw each satisfaction score as a bar by repeating the block character █ as many times as the score in column D.

Remember

• Text between two markers: MID(text, FIND(start)+1, FIND(end)-FIND(start)-1).
• Count a character: LEN(text)-LEN(SUBSTITUTE(text, char, "")).
• Last word: TRIM(RIGHT(SUBSTITUTE(text, " ", REPT(" ", 50)), 50)), or TEXTAFTER(text, " ", -1).
• REPT("█", n) draws a bar n characters long.

Previous: Mapping codes with CHOOSE and SWITCH · Next: Repairing numbers and names