Text functions in Grid: split, join, tidy and match patterns

Not all data arrives tidy. Names come with titles attached, product codes carry a prefix you don't need, and an export from another system squeezes three fields into one cell. Grid by Zaweba has 47 text functions for pulling text apart, putting it back together and checking it against a pattern.

Splitting text without the fiddly bits

Older spreadsheets made you count characters with combinations of FIND, LEFT and MID to cut a piece out of a cell. Those functions are still in Grid, but three newer ones save most of the effort. TEXTBEFORE returns the text before a delimiter, so the part of an email address before the at sign is one short formula. TEXTAFTER returns the text after it. TEXTSPLIT breaks a cell at a delimiter into several pieces and spills them across the neighbouring cells, so "Smith, John, Leeds" becomes three cells from a single formula.

The difference between TEXTSPLIT and Text to Columns on the Data tab is worth knowing. Text to Columns splits the cells once and leaves fixed values behind. TEXTSPLIT is a formula, so if the source cell changes the pieces update too. Use the command for a one-off clean up, and the function when the source will keep changing.

Pattern matching with REGEX

Grid includes REGEXTEST, REGEXEXTRACT and REGEXREPLACE, which use regular expressions: a compact way of describing a pattern of characters. REGEXTEST tells you whether a cell matches a pattern, which is a quick check for things like postcodes or order numbers that should follow a set shape. REGEXEXTRACT pulls out the first part of the text that matches, such as a number buried in a description. REGEXREPLACE swaps every match for something else, for stripping out unwanted characters in one go.

Regular expressions take a little learning, but you don't need many patterns to be useful. Digits, letters, "one or more" and "start of text" cover a lot of everyday tidying. Test a new pattern on a few sample cells in a spare column before you apply it to the whole list.

TEXT and number formats

TEXT turns a number into text using a number-format code, the same codes you'd use in Format Cells. That's how you build a sentence like "Total due: 1,250.00 by 31 March" from a number and a date, with the formatting you want and no stray decimals. The format rules are the standard ones: sections and conditions, fractions, scientific notation, scaling commas and locale long dates.

Remember that the result of TEXT is text. It looks like a number, but sums and averages will skip it. Keep the real figure in one cell for calculating and use TEXT only where the number is being shown inside a label or message.

Joining, tidying and comparing

The family also covers the everyday jobs: joining pieces with a separator, trimming extra spaces, changing case, measuring length, and finding or substituting a piece of text. A typical tidying pass might trim a name column, put it into proper case and join first and last names into a display name.

When text that should match won't, check for hidden spaces and differences in case first. Those two account for most lookups that fail on text keys.

When a formula isn't the answer

For a column that needs cleaning just once, a formula in a helper column followed by Paste Special to paste values is often the tidiest route. Paste over the original, delete the helper and the sheet is left with clean data and no extra formulas. If you've connected your own AI account, the Clean up data action is another option, and Fill down from examples can follow a pattern you show it.

Text functions are the unglamorous part of a spreadsheet, but in Grid by Zaweba they're often what turns a pasted mess into a list you can sort, filter and look things up in.