1

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:

http://en.wikipedia.org/wiki/E.164

  • Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed. – bwDraco Feb 22 '15 at 19:58
  • oh, I'm not looking for code. I was thinking there might be a "thing" to click on. Some sort of utility? – Thufir Feb 22 '15 at 20:05
  • This question is inappropriate for this site. And as you describe it there is no “thing” to click on. You need to program something to do this “thing.” – JakeGould Feb 22 '15 at 20:10
  • oh, Ok. I just thought it might be such a frequently performed action that there might be a utility, or something. Now I know :) – Thufir Feb 22 '15 at 20:14
  • 1
    I don't see why this question would be inapprpriate. Granted, it does not show much research effort but it is still answerable. – slhck Feb 22 '15 at 21:01
1

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

https://docs.google.com/spreadsheets/d/1lWPRj_9uQW3BYUGUqKqW_NDlJ0VH0wUmf04ClVk6UEg/edit?usp=sharing

| improve this answer | |
  • It splits them, rather than strip. but thank you. I know now to just use a script of some sort. – Thufir Mar 20 '15 at 0:08
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.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.