phone number format question

Originally Posted by bonz

Hello,

I have a database I am trying to clean up containing over 25000 rows of data.

I am trying to change all the cells in the phone number columns to show in this format:

123.456.7890

Currently I have the following formats...

(123) 456-7890
123-456-7890
1234567890
12345 67890
etc...

I have removed all non numbers (under a different filename) and made each cell appear in the following format:

1234567890

I then tried the "format cell:special:phone number" feature, but it doesn't have the 123.456.7890 format option. It appears (123)456-7890. Also, it doesn't actually change the value in the cell. This is a problem because this data, once cleaned, will be exported to a separate database. So, the values of the cells must show exactly how they are supposed to appear.

Any suggestions would be greatly appreciated.

Many Thanks,

Bonz

easiest would be to use Replace All, get rid of space ( ) , -
and then select

=Left(A1,3)&"."&Mid(A1,4,3)&"."&Right(A1,4)

and formula fill that down the column

hth
---
amended, there are three kinds of people in this world, those who can count, and those who can't.

Thank-You! Thank-You! Thank-You!

You just made my life a whole lot easier. I already tried it and it works perfectly.

Thanks again.

Sincerely,
Bonz

Originally Posted by bonz

Thank-You!

You just made my life a whole lot easier. I already tried it and it works perfectly.

Thanks again.

Sincerely,
Bonz

Good to see it work for you, and thanks for the response.

---