A Couple of Useful Google Sheets Formulae

2022-03-24 10:00 AM Comment(s) By Cavan Kelly

Clean up data quickly using these two formulae

I wrote these several years ago in response to a question about cleaning up a list of phone numbers for input into a PBX. This morning, I needed to perform a similar cleanup and went hunting for the post. Hopefully, by re-posting here I'll find them quicker next time and, in the meantime, someone else might find them useful.


This formula operates on a cell (A1) and strips out any characters specified between the quotation marks:

=CONCATENATE(SPLIT(A1,"() -."))


This formula operates on a cell (A1) and strips out any characters NOT specified between the quotation marks:

=CONCATENATE(SPLIT(A1,CONCATENATE(SPLIT(A1,".0123456789"))))


In both cases, the result is a STRING. Of course, you can convert it to a VALUE by including either of the formulae within a VALUE function.


UPDATE: These formulae also work in Zoho Sheet simply by changing the comma delimiters to semi-colons.




Share -