phone number formatting

**djbetterly** · 12-08-2009, 05:56 PM

I'm working on a database file that has both foreign and domestic phone numbers in it. I need to add a "+" sign in front of all the numbers, what is the easiest way to go about this? Something is off in the formatting because when ever I try to do a custom format it doesn't change anything, nor does it change when I convert the format to a number. I am pretty sure all spaces have been removed but I cannot be certain. I've attached a copy for you to look at.

Thank you,

Dustin

**teylyn** · 12-08-2009, 06:04 PM

Hi, are the samples "before" or "after", i.e do you want to add another + sign in front or are you showing the result?

In any case, the data is text, so there's not much you can do to change that with formatting.

if you have a "real" number, like

12124435546

then you can use a custom format like

"+"0-000-000-0000

to get

+1-212-443-5546

hth

**NBVC** · 12-08-2009, 06:06 PM

What is in the blank rows in between?

Assuming text, then in A2 use formula:

=IF(LEFT(A2)="+",A2,IF(ISNUMBER(RIGHT(A2,4)+0),"+"&A2,A2)) copied down

Then you can copy this column and Edit|Paste Special >> Values over the original and delete the formula column.