Excel help number umpteen

[johnnypohlfan]

I'm trying to convert a phone number to a different format. What's populated in the cell currently is (xxx) xxx-xxxx and i would like to change it to xxx-xxx-xxxx. I've tried messing around with using the Phone Number format as a template and making some changes but cannot get it to work.

I have a list of several hundred that need to be changed so if i could create a Custom format to do it, that would be great. Is this even possible?

Thanks for any help on this one!

 

[JF_Gophers]

Re: Excel help number umpteen

I'm trying to convert a phone number to a different format. What's populated in the cell currently is (xxx) xxx-xxxx and i would like to change it to xxx-xxx-xxxx. I've tried messing around with using the Phone Number format as a template and making some changes but cannot get it to work.

I have a list of several hundred that need to be changed so if i could create a Custom format to do it, that would be great. Is this even possible?

Thanks for any help on this one!

Is the cell formatted to start with? Or is it actually typed in with the ( ). Sounds like it was typed in with the parenthesis and that's probably why the cell format change is not working.

If changing format doesn't work you may consider a formula to change them to number only, and then format after that. Or simply use the formula to put in the dashes (-) as well.

A) Just an example, may need to be adjusted if the format varies. Don't have Excel reinstalled to verify I typed in the formula exactly right.

Ph# Cell: (800) 555-1234

Formula Cell: =CONCATENATE(MID($A1,2,3),"-",MID($A1,(SEARCH(" ",$A1),(LEN($A1)-SEARCH(" ",$A1)))


B) Another option, do a find/replace on that column and delete all the ( ) and -, then do change format.

 

[johnnypohlfan]

Re: Excel help number umpteen

Is the cell formatted to start with? Or is it actually typed in with the ( ). Sounds like it was typed in with the parenthesis and that's probably why the cell format change is not working.

If changing format doesn't work you may consider a formula to change them to number only, and then format after that. Or simply use the formula to put in the dashes (-) as well.

A) Just an example, may need to be adjusted if the format varies. Don't have Excel reinstalled to verify I typed in the formula exactly right.

Ph# Cell: (800) 555-1234

Formula Cell: =CONCATENATE(MID($A1,2,3),"-",MID($A1,(SEARCH(" ",$A1),(LEN($A1)-SEARCH(" ",$A1)))


B) Another option, do a find/replace on that column and delete all the ( ) and -, then do change format.

Thanks a million. A couple find/replaces and I'm in business.