formatting issue / rookie player

**scottfree** · 09-07-2008, 03:17 PM

i am running excel 2003 and have limited knowledge of all it's functions thus seeking expert advice having exhausted my attempts to trouble shoot on my own.

i have a database of leads i converted from notepad/text to excel, a feat in itself i'm proud to have completed.

my problem:
the data contains a string of phone numbers in it's own column in which i am seeking to convert from, e.g. (123) 456-7890 to read 1234567890.

i have tried numerous times from the toolbar: format; cells; special; phone number; and custom without any success removing the characters noted above; parentheses and hyphen contained in the phone number.

is there a simple solution?

thank you for your help, scott

**oldchippy** · 09-07-2008, 03:20 PM

Hi,

Try this

=MID(A1,2,3)&MID(A1,7,3)&MID(A1,11,4)

**scottfree** · 09-07-2008, 03:22 PM

thanks oldchippy,

could you tell me please where to insert code?

**oldchippy** · 09-07-2008, 03:24 PM

Hi,

If your text is listed in column A, then you would put this in B1, then auto-fill the formula down the column, Then select the range in column B, copy and paste special > values, then delete column A

**martindwilson** · 09-07-2008, 04:43 PM

in case they are not in same format eg (123) 456-7890 (0123) 456-7890
you can also use =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"(",""),")",""),"-","")

**VBA Noob** · 09-07-2008, 04:46 PM

Or with some code

Press Alt + F11 > insert Module > paste in the below > select a cell in the column > press Alt + F8 > select run ChangeString

Please Login or Register  to view this content.

VBA Noob

**jadams@smcoe** · 05-26-2011, 03:56 PM

I'm having the same problem. I created a report from our database at work and the format is with parentheses and dashes. I tried both formulas listed and was out of luck. I thought maybe formatting under "custom" and putting something similar to [<=(999)]###-####; ########## would work.
This is driving my nuts.