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,CONCATEN
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.