I'm looking for both a quick and dirty, as well as easily repeatable, solution to strip non-numeric data from a column in a spreadsheet for import to a MySQL database. Example:

(123)456-7890 fred 

That’s all in one cell in Microsoft Excel; one column. The entries are not in a standardized format.

• Most rows don’t have the extra name in there, but some do. The name is superfluous, I only need the numbers.
• For international dialing—I suppose, but I'm not sure—there’s a concern about leading zeros, if the phone number is treated as as an integer. But I'm not concerned. Numeric result as an integer is what I’m after.
• The telephone number can be in various common formats, not necessarily matching this example of 13 characters, with area code in parentheses and hyphen separation for the last 4 digits. For example, it might have a leading digit like 0 or 1, the area code could be separated by a hyphen instead of being enclosed in parentheses, etc.

I don't want to preclude any particular approach, but the problem would seem to lend itself to a solution that strips out non-numeric characters rather than attempting to decipher and parse all of the possible contents.

I imported it to Google Spreadsheet to have a back-up. I’m a Linux user, so don’t have access to native Microsoft Excel, which is how it was received. I do have access to Libre Office, however.

I'm aware that I can use MySQL regex for this purpose as explained in this thread.

Which gives a few options. I was just thinking that within LibreOffice or Google Spreadsheet there might be a tool for this. I’m a bit leery of running it through a web app, depending on whose webapp it is.

The desired format is:

7

2 Answers

there you go, this is for Google spreadsheets:

=SPLIT("(123)456-7890 fred"," ")

You can extend the same formula for all of your records

=SPLIT(A1," ")

Or you can try something more elaborated with the array formula...

actually if you are looking for insert or update statements you can get them from a google spreadsheet to, I recommend this

1

An alternative would be to use Find&Replace to Replace ( with nothing, ) with nothing, - with nothing (this part could be tricky if there are multiple different kinds of characters that look like dashes, but if they are all standard dashes it's easy), and space with nothing. Four characters typed into the "Find" field, the "Replace All" button pressed four times, and done.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy