Text Cleaning Formulas with AI: TEXTSPLIT, TRIM and REGEX

Part of the free Module 13: Excel Formulas with AI · Lesson 8 of 9 · Full Excel course

Updated on 5 September 2026 · TRIM, CLEAN, SUBSTITUTE, LEFT, RIGHT, MID, FIND, PROPER and VALUE work in every version. TEXTSPLIT, TEXTBEFORE and TEXTAFTER need Excel 365 or Excel 2024. REGEXTEST, REGEXEXTRACT and REGEXREPLACE are Excel 365 only, on the Current Channel, rolled out through 2024 and 2025.

Text cleaning formulas with AI means describing your messy column to a chat assistant in plain English and getting back a working Excel formula that trims, splits or extracts the part you need. The skill is not the formula, it is the description: give the raw strings, the rules and your Excel version, and the answer is usually right first time.

What messy text means and how to describe it to AI

Exported data is rarely clean. The same column can carry padded spaces, non-breaking spaces copied from a web page, line breaks from a CRM note field, inconsistent capitals, names in one cell that should be two, and numbers stored as text. An assistant cannot see any of that. It can only see what you paste, so paste the ugly reality rather than a tidy summary of it.

The single most effective habit is to show three or four raw sample strings, including the awkward ones, and say what the answer should be for each. Compare these two requests:

Weak: how do I clean up my name column in Excel?

Strong:
Goal: split a name column into Last name and First name.
Data: Sheet Customers, column A, rows 2 to 500, header in A1.
Raw samples exactly as they appear:
  "Sharma, Priya"
  "O'Brien, Daniel"
  "  Van Der Berg, Anna "
  "Khan,Imran"
Rules: some rows have no space after the comma, some have leading
spaces, some contain a non-breaking space instead of a normal space.
Version: Excel 365, English (United Kingdom), comma argument separator.
Output: give the two formulas only, then one sentence each.

The strong version is the five part prompt template taught earlier in this course: goal, data layout, rules and edge cases, version and locale, output format. Text cleaning is the topic where the rules line matters most, because the edge cases are the whole job.

Function map: classic, modern and REGEX

Before you judge an answer, know which shelf it came from. If the assistant offers TEXTSPLIT and you are on Excel 2016, the formula is not wrong, it is simply not available to you. Tell it your version and it will pick from the correct shelf.

Function What it does Available in
TRIM Removes leading, trailing and repeated normal spaces All versions
CLEAN Removes non-printing control characters, codes 0 to 31 All versions
SUBSTITUTE Replaces one piece of text with another, by instance if needed All versions
LEFT, RIGHT, MID Take characters from a fixed position All versions
FIND, SEARCH Locate a delimiter; FIND is case sensitive, SEARCH is not All versions
PROPER, UPPER, LOWER Fix capitalisation All versions
VALUE Turns text that looks like a number into a real number All versions
NUMBERVALUE Same, but you state the decimal and group separators 2013 and later
TEXTJOIN, CONCAT Join pieces back together, optionally skipping blanks 2019 and later
TEXTBEFORE, TEXTAFTER Return the text either side of a delimiter, no FIND arithmetic 365 and 2024
TEXTSPLIT Splits one cell across columns or down rows in one formula 365 and 2024
REGEXTEST Returns TRUE or FALSE if a pattern matches 365 only
REGEXEXTRACT Pulls the matching piece out of the text 365 only
REGEXREPLACE Replaces every match with something else 365 only

One warning that saves hours. Assistants are trained on a great deal of Google Sheets content, so they cheerfully offer REGEXMATCH, SPLIT, ARRAYFORMULA and QUERY. None of those exist in Excel and all of them return #NAME?. The Excel equivalents are REGEXTEST, TEXTSPLIT, native dynamic arrays and Power Query. If a suggested function is not in the table above, check it before you trust it.

Ready-to-copy prompts for text cleaning

Each prompt below is written to the five part template. Under each one is the formula a good assistant returns, a small sample table and the result you should see. Adjust sheet names and ranges to match your file.

1. Split Last, First into two columns

Goal: split "Last, First" in column A into Last name and First name.
Data: Sheet Customers, A2:A500, header Full Name in A1.
Rules: the space after the comma is not always present; some rows
have leading or trailing spaces; keep apostrophes such as O'Brien.
Version: Excel 365, English (UK).
Output: two formulas for B2 and C2 only, plus one Excel 2016
alternative for each, then one sentence of explanation.

The 365 answer uses the delimiter functions, so there is no character counting:

B2 (Last name)   =TRIM(TEXTBEFORE(A2,","))
C2 (First name)  =TRIM(TEXTAFTER(A2,","))

The Excel 2016 fallback does the same work with FIND and LEN:

B2 (Last name)   =TRIM(LEFT(A2,FIND(",",A2)-1))
C2 (First name)  =TRIM(MID(A2,FIND(",",A2)+1,LEN(A2)))
A: Full Name B: Last name C: First name
Sharma, Priya Sharma Priya
O’Brien, Daniel O’Brien Daniel
Van Der Berg, Anna Van Der Berg Anna
Khan,Imran Khan Imran

Wrapping both in TRIM is what makes the fourth row work. Without it, the row with no space after the comma is fine but the rows with a space return a leading space you cannot see.

2. Remove non-breaking spaces and double spaces

Goal: clean a company name column pasted from a web page.
Data: Sheet Import, A2:A2000.
Rules: cells contain leading and trailing spaces, repeated spaces
between words, non-breaking spaces (character 160) and occasional
line breaks. TRIM alone is not fixing them.
Version: Excel 2019 or later.
Output: one formula for B2, then explain why TRIM alone failed.
=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," "))

Read it from the inside out. CLEAN strips control characters such as the line break, character 10. SUBSTITUTE turns every non-breaking space, character 160, into a normal space. Only then can TRIM collapse the runs of spaces and remove the ones at each end.

A: raw value (hidden characters marked) LEN(A2) B: result LEN(B2)
[space][space]Acme Ltd[space] 11 Acme Ltd 8
Acme[NBSP]Ltd 8 Acme Ltd 8
Acme[space][space]Ltd 9 Acme Ltd 8
Acme Ltd[line break] 9 Acme Ltd 8

This is the single most common data cleaning surprise in Excel. A non-breaking space looks identical to a space on screen, TRIM ignores it completely, and your lookup keeps returning #N/A. Use =LEN(A2) next to the cell and compare with =LEN(TRIM(A2)): if the numbers refuse to move, character 160 is the culprit.

3. Extract an invoice number from a sentence

Goal: pull the invoice reference out of a free text notes column.
Data: Sheet Notes, A2:A800.
Rules: the reference always looks like INV-2024-0173, that is INV
then a hyphen, four digits, a hyphen and four digits. Some rows
have no reference; those should return a blank, not an error.
Version: Excel 365 Current Channel, and also give a fallback that
works in Excel 2019.
Output: both formulas only, then one sentence each.

The regular expression version reads almost like the rule you wrote:

=IFERROR(REGEXEXTRACT(A2,"INV-[0-9]{4}-[0-9]{4}"),"")

The classic version relies on the reference always being 13 characters long:

=IFERROR(MID(A2,FIND("INV-",A2),13),"")
A: Note text B: Extracted reference
Payment received for INV-2024-0173 on 12 March INV-2024-0173
Please re-issue INV-2025-0042, the VAT is wrong INV-2025-0042
Credit note against invoice 4471 (blank)
Both INV-2024-0180 and INV-2024-0181 are overdue INV-2024-0180

The last row shows the difference between the two. REGEXEXTRACT returns the first match by default, and you can ask for all of them by setting its return mode argument to 1. The MID version can only ever find the first occurrence. Note also that MID quietly breaks the day someone writes a five digit reference, while the pattern simply stops matching, which is the safer failure.

4. Validate an email address pattern

Goal: flag rows where the email address is not a valid pattern.
Data: Sheet Contacts, column D, rows 2 to 1200.
Rules: must have some characters, an at sign, a domain, a dot and
at least two letters after the dot. Do not check whether the
mailbox really exists. Show Valid or Check, not TRUE or FALSE.
Version: Excel 365 Current Channel.
Output: one formula for E2 and a plain English breakdown of the
pattern, one line per piece.
=IF(REGEXTEST(D2,"^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+[.][A-Za-z]{2,}$"),"Valid","Check")
D: Email E: Result Why
priya@example.com Valid Matches the whole pattern
p.sharma+news@mail.co.uk Valid Dots and a plus sign are allowed before the at sign
priya@example Check No dot and no letters after it
priya example.com Check No at sign at all

Pattern validation catches typing mistakes, nothing more. A perfectly formed address can still bounce. Treat the Check column as a review queue for a human, not as a delete list.

5. Split a comma list down into rows

Goal: one cell holds several products separated by commas. Put each
product on its own row and remove the stray spaces around them.
Data: Sheet Orders, cell A2 holds "Laptop, Monitor ,Keyboard,  Mouse".
Rules: spacing around the commas is inconsistent; ignore empty
pieces caused by two commas together.
Version: Excel 365.
Output: one formula only, then one sentence.
=TRIM(TEXTSPLIT(A2,,",",TRUE))
A2 Spilled result
Laptop, Monitor ,Keyboard, Mouse Laptop
Monitor
Keyboard
Mouse

The empty second argument is the important detail. TEXTSPLIT takes a column delimiter first and a row delimiter second, so leaving the first one out and supplying the comma as the second argument sends the pieces down instead of across. The fourth argument, TRUE, ignores empty pieces. TRIM then tidies each item because it happily works on the whole spilled array at once.

6. Turn text that looks like a number into a number

Goal: convert a text column of amounts into real numbers I can sum.
Data: Sheet Export, column A. Values arrive as 1,234.56 from the US
system and as 1.234,56 from the European system, some carry a
currency symbol and some carry a unit such as kg.
Version: Excel 365, Windows, English (UK) regional settings.
Output: one formula per case, in a short table, formulas only.
A: text value Formula Result
1,234.56 =NUMBERVALUE(A2,".",",") 1234.56
1.234,56 =NUMBERVALUE(A3,",",".") 1234.56
$1,299.00 =NUMBERVALUE(SUBSTITUTE(A4,"$",""),".",",") 1299
45 kg =VALUE(SUBSTITUTE(A5," kg","")) 45
78 with a non-breaking space =VALUE(TRIM(SUBSTITUTE(A6,CHAR(160)," "))) 78

NUMBERVALUE is the honest choice when the file came from another country, because you state the separators instead of hoping Windows guesses them. VALUE follows your regional settings, which is exactly what you want for home grown data and exactly what fails on an import.

Asking AI to explain a REGEX pattern piece by piece

A pattern you cannot read is a pattern you cannot maintain. Whenever an assistant hands you one, ask for a breakdown before you paste it into a workbook that other people use.

Explain this Excel REGEXEXTRACT pattern one piece at a time, in a
table with two columns, piece and meaning, in plain English:
INV-[0-9]{4}-[0-9]{4}
Then tell me one realistic value it would wrongly match and one
realistic value it would wrongly miss.
Piece Meaning
INV- Those exact four characters, capital letters and a hyphen
[0-9] Any single digit from 0 to 9
{4} Exactly four of the thing before it
- A literal hyphen
[0-9]{4} Four more digits
^ and $ In the email pattern, anchor the match to the start and end of the cell
+ One or more of the thing before it
{2,} Two or more of the thing before it

Assistants usually return the shorthand for a digit, a backslash followed by the letter d, and the shorthand for a word character, a backslash followed by w. Excel accepts both. This lesson writes [0-9] and [A-Za-z] instead because they say the same thing, they survive being copied through email and web pages, and a colleague reading the workbook next year can work out what they mean. Ask for that style explicitly in your prompt if you prefer it.

Always finish with the second half of that prompt, the wrongly matched and wrongly missed values. It forces the model to think about edge cases, and it hands you two ready made test rows.

Worked example: clean an exported customer list

An export lands with three problem columns: names in one lowercase field, emails with stray spaces and random capitals, and invoice references buried in a notes field.

A: Name B: Email C: Notes
sharma, priya [space]PRIYA@Example.com[space] Order INV-2024-0173 shipped
o’brien, daniel DANIEL@Example.com Refund for INV-2025-0042 approved
van der berg, anna anna@Example.COM[space] No invoice reference yet
khan,imran [space]imran@example.com Order INV-2024-0180 delivered
  1. In D2 normalise the raw name so every later step sees the same thing: =TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," ")). Row 1 returns sharma, priya.
  2. In E2 take the surname and fix its case: =PROPER(TRIM(TEXTBEFORE(D2,","))). Row 1 returns Sharma, row 2 returns O’Brien, row 3 returns Van Der Berg.
  3. In F2 take the first name: =PROPER(TRIM(TEXTAFTER(D2,","))). Row 4 returns Imran even though there is no space after the comma.
  4. In G2 standardise the email: =LOWER(TRIM(B2)). Row 1 returns priya@example.com.
  5. In H2 pull the reference out of the note: =IFERROR(REGEXEXTRACT(C2,"INV-[0-9]{4}-[0-9]{4}"),""). Row 3 returns a blank, the other rows return their reference. On Excel 2019 use =IFERROR(MID(C2,FIND("INV-",C2),13),"") instead.
  6. In I2 rebuild a display name: =TEXTJOIN(" ",TRUE,F2,E2). Row 1 returns Priya Sharma.
  7. Select D2:I5, copy, then use Home > Paste > Paste Special > Values before you delete the original columns, otherwise every formula turns into a reference error.

Check the PROPER column by eye before you trust it. PROPER capitalises the letter after any non letter, which is right for O’Brien but wrong for McDonald, which becomes Mcdonald, and wrong for initials such as PLC, which becomes Plc. Fix those with a small SUBSTITUTE list or a manual pass.

Tips and common mistakes

  • Show the raw string, not a description of it. Paste four real values including the worst one. An assistant that has seen the mess writes a formula that survives it.
  • State your Excel version in every prompt. Without it you will be handed TEXTSPLIT and REGEXEXTRACT by default, because that is what most recent training data uses.
  • Reject Google Sheets functions on sight. REGEXMATCH, SPLIT, ARRAYFORMULA and QUERY do not exist in Excel. Ask for the Excel equivalent rather than trying to make them work.
  • Suspect character 160 whenever TRIM does nothing. Compare LEN before and after; if the length is unchanged, substitute CHAR(160) first.
  • Wrap extraction formulas in IFERROR. FIND returns #VALUE! and REGEXEXTRACT returns #N/A when there is no match, and one bad row should not spoil the column.
  • Clean into new columns, never over the original. Keep the raw import until the cleaned version has been checked, then paste values and delete.
  • Ask for the pattern in plain character classes. Requesting [0-9] rather than the backslash shorthand makes the formula readable to whoever inherits the file.
  • Consider Power Query for repeat imports. Formulas are perfect for a one off tidy up; a file you receive every week deserves a query you refresh with one click.

Errors and how to fix them

Error or symptom Cause Fix
#NAME? on TEXTSPLIT, TEXTBEFORE or TEXTAFTER You are on Excel 2021 or earlier Use the LEFT, MID, FIND and LEN fallback, or upgrade to 365 or 2024
#NAME? on REGEXTEST, REGEXEXTRACT or REGEXREPLACE Not on Microsoft 365 Current Channel, or the update has not reached you Update Office, or rewrite with SUBSTITUTE, FIND and MID
#NAME? on REGEXMATCH, SPLIT or ARRAYFORMULA The assistant gave you Google Sheets syntax Ask again for Excel only functions and name your version
#SPILL! TEXTSPLIT needs empty cells and something is in the way Clear the cells the result needs, or move the formula to a clear area
#VALUE! from a MID or LEFT formula FIND could not locate the delimiter in that row Wrap in IFERROR, or use SEARCH if the case might differ
#N/A from REGEXEXTRACT, TEXTBEFORE or TEXTAFTER No match and no delimiter in that row Wrap in IFERROR and return a blank or a marker such as Not found
#VALUE! from VALUE or NUMBERVALUE A currency symbol, unit or wrong separator is still in the text SUBSTITUTE the symbol out first, then state the separators in NUMBERVALUE
Lookups still fail on cleaned text A non-breaking space or a trailing invisible character remains Apply TRIM, CLEAN and SUBSTITUTE with CHAR(160) to both sides of the lookup

Practice exercise

  1. Type four names in the form Last, First, deliberately adding a leading space to one and removing the space after the comma in another. Ask an assistant for the split formulas, then check all four rows.
  2. In a spare cell enter ="Acme"&CHAR(160)&"Ltd". Confirm that TRIM does not shorten it, then fix it with the SUBSTITUTE and CLEAN combination and watch LEN drop.
  3. Write five sentences containing invoice references and one without. Extract the reference with both the REGEXEXTRACT and the MID method, and confirm the blank row behaves the same way in each.
  4. Build a validation column for ten email addresses with REGEXTEST, including two you expect to fail. If you are not on 365, ask an assistant for an ISNUMBER and SEARCH alternative and compare the results.
  5. Give an assistant a pattern it wrote and ask it to name one value the pattern would wrongly match and one it would wrongly miss. Add both to your sample data as permanent test rows.

Key takeaways

  • Describe the mess, not the wish. Three or four raw sample strings plus the expected answers produce a working formula far more reliably than a one line question.
  • Always state your Excel version. TEXTSPLIT and the delimiter functions need 365 or 2024, and the REGEX family is 365 Current Channel only.
  • TRIM does not remove character 160, the non-breaking space. Use TRIM with SUBSTITUTE and CLEAN together on any imported column.
  • REGEXMATCH, SPLIT, ARRAYFORMULA and QUERY are Google Sheets functions and return #NAME? in Excel, no matter how confidently they are offered.
  • Ask for a piece by piece explanation of every pattern, plus one value it would wrongly match and one it would wrongly miss.
  • Clean into new columns, wrap extractions in IFERROR, then paste values before you delete the original data.

Related lessons

Frequently asked questions

Why does TRIM not remove the spaces in my imported column?

TRIM only handles the normal space, character 32. Text copied from web pages and PDFs often contains the non-breaking space, character 160, which looks identical but survives TRIM untouched. Use =TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160)," ")) instead. CLEAN also removes line breaks and other control characters that TRIM ignores.

Which Excel versions have REGEXEXTRACT and REGEXTEST?

The REGEX family, meaning REGEXTEST, REGEXEXTRACT and REGEXREPLACE, is Microsoft 365 only. It rolled out through the Current Channel across 2024 and 2025, so a 365 subscriber on a slower channel may still not have it. Excel 2024, 2021, 2019 and 2016 never get these functions, so use SUBSTITUTE, FIND and MID there.

Why did the AI give me REGEXMATCH and it does not work?

REGEXMATCH, along with SPLIT, ARRAYFORMULA and QUERY, belongs to Google Sheets. Assistants mix the two products because both are spreadsheets and the training data overlaps. Excel returns #NAME? for all of them. Say Excel and your version in the prompt, and ask it to confirm each function exists in Excel.

Should I use TEXTSPLIT or Text to Columns?

Text to Columns is a one off wizard that overwrites cells and does not update when the source changes. TEXTSPLIT is a formula, so it recalculates automatically and you can wrap it in TRIM or feed it into other formulas. Use the wizard for a single tidy up and TEXTSPLIT for anything you will repeat.

Can AI clean the data for me instead of writing a formula?

Copilot in Excel can insert a formula column and Python in Excel can transform a whole table, but for a repeatable process a formula or a Power Query step is better. A formula is auditable, it recalculates and a colleague can read it. Ask AI for the method, then keep the method in the workbook.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.

← PreviousIF, IFS, SWITCH, AND and OR Formulas Written with AIAll lessonsExcel Formulas with AINext →Date and Time Formulas with AI: Ages, Deadlines and Working Days