Remove non-numeric characters
=REGEXREPLACE(A2,"\D","")Google Sheets · result on the sample sheet: 5 rows (B2:B6)
Describe what you need and get a working REGEXREPLACE formula for Excel and Google Sheets, tested on real data before you copy it. REGEXREPLACE replaces the parts of a text that match a regular expression.
Each formula below was calculated on its own sample sheet. Open one to see the sheet, why it works, and to try it yourself.
=REGEXREPLACE(A2,"\D","")Google Sheets · result on the sample sheet: 5 rows (B2:B6)
=REGEXREPLACE(A2,"^(.+), (.+)$","$2 $1")Google Sheets · result on the sample sheet: 5 rows (B2:B6)
=REGEXREPLACE(REGEXREPLACE(A2,"\D",""),"^1?(\d{3})(\d{3})(\d{4})$","($1) $2-$3")Google Sheets · result on the sample sheet: 5 rows (B2:B6)
=TRIM(REGEXREPLACE(A2,"[^A-Za-z0-9 ]",""))Google Sheets · result on the sample sheet: 5 rows (B2:B6)
=VALUE(REGEXEXTRACT(A2,"-?\d[\d,]*\.?\d*"))Google Sheets · result on the sample sheet: 5 rows (B2:B6)
=TRIM(SUBSTITUTE(REGEXREPLACE(A2,"<[^>]+>"," "),"&","&"))Google Sheets · result on the sample sheet: 5 rows (B2:B6)
=MID(A2,MATCH(TRUE,MID(A2,SEQUENCE(LEN(A2)),1)<>"0",0),LEN(A2))Excel · result on the sample sheet: 5 rows (B2:B6)
=TEXTBEFORE(A2," ",-1)Excel · result on the sample sheet: 5 rows (B2:B6)
You describe the task in plain English, and it writes the formula, using REGEXREPLACE where that's the right tool, for your table or a sample one. The formula is run in our engine in Excel and Google Sheets mode before you see it, so you get its result, not just a guess.
REGEXREPLACE replaces the parts of a text that match a regular expression.
Yes. Each example below opens a page where you try the formula in a live grid and it's checked as you go, and the REGEXREPLACE function page has a practice exercise.