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:
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: