Stripping (but retaining) Phone Extensions

[graybeam]

We're doing a data migration and I have a phone number field with no standard of format. Some phone numbers have (-) dashes some have (.) periods between sets of numbers, and some have phone extensions on the end with either x###, X ####, ext ###, and some other variatoins. The extensions will have their own numeric field in the new system and the phone numbers should be formatted ###.###.####. I might add there are also non US numbers as well that should be formatted with periods as well. I know this is a mess, but does anyone have any ideas to clean this in one or two motions?

Thanks!

 

[pagrender]

Welcome to the board.

Can you use the Replace (Control + H) command to clean up your data? You can replace all the "-" with "." and it would put your data in a consistent format for you.

From there you can probably use Text to Columns to move all the extensions to their own column.

 

[graybeam]

Thank you for the quick reply! Yes I can use F&R to take care of the hyphens, but text to columns will not work to strip the extensions due to the lengthy forign numbers (up to about 16 digits). I was experimenting with the macro below and it works (sort of) to pul the extensions out, but the FIND command is case sensitive so only works with one extension value. I still have "ext", "ext ", "X", "X ", and "x ". I was looking for a macro that uses maybe a string validation to search for multiple criteria and pull the integers after those values. If, in the macro, I could just add the finding and replacing of (-) hyphens to (.) periods, it would be nice to have it all in one step, but that is just for convenience. I suppose it is all for convenience in the end

=RIGHT(B3,LEN(B3)-FIND("x",B3))

 

[West Man]

Replace - with . then use LEN to get the length of each phone number. Sort on this length to categorize your data, then handle each category seperately.

 

[graybeam]

Replace - with . then use LEN to get the length of each phone number. Sort on this length to categorize your data, then handle each category seperately.

That is a fair idea. I tried this and the data is still so unpredictable that even 20 digit phone numbers don't specifically house numbers with extensions. The forign numbers are what is killing the text-to-column solutions. There is a forign number (it seems) for every phone number LEN from 13 characters to 20.

I think what I need is a macro that counts the number of digits, finds a number of strings (x, ext, ect.) and strips the extension into a separate column. Then maybe a second macro to strip the phone numbers to a column of their own minus the extensions as well.

 

[pagrender]

Hi Graybeam,

The only other thing I can think of is to use the SEARCH formula as it's not case-sensitive (as FIND is).

However, we'll have to let one of the Excel experts come up with how to apply the formula.

I'll keep an eye on this thread as I would like to see how it's done too. Good luck!

Pete

 

[graybeam]

I have come to a semi-simple solution, your ideas got my juices flowing! If I search for "x" it does a case-insensitive search and finds all values with x (wich are x, X, ext, etc.), which covers most of the extensions. If I then replace x with x plus format the cells with a color, I can filter by the color and use the text-to-column for the colored cells only, to pull the extensions out. It worked great!

 

[pagrender]

Great solution!

I was working on a formula that combined the MID and SEARCH functions but you came up with a solution much quicker.

Pete

 

[graybeam]

Thanks! I was kind of proud :D