Costa Rica phone numbers

[napoelonguerra]

Hi All,

as of next Wednesday (March 20th) all the phone numbers in Costa Rica will change to 8 (eight) digits.
The present land-line-numbers start with 2 / 4 / 5 / 6 / 7. The present cell-phone lines start with either 3 / 8 / 9.
All the land-lines will get a prefix "2".
All the cell phone lines will get a prefix "8".

For example:
244-3563 will become 2244-3563
645-5052 will become 2645-5052
370-2796 will become 8370-2796
824-4950 will become 8824-4950

...and so on.

I am using Outlook '07 and exported all my contacts to an Excel file. So far so good.

Now: what would be the easiest formula to convert all those numbers - without touching my international contacts - usually starting with "00".

I've already started to convert some columns with "CONCATENATE", but that is a tedious operation, since I have to sort and resort to get the different columns lined up in one batch to make things easier.

Icing (on the cake) would be if later, after having converted everything, I could use a format like X-XXX-XXXX to make the numbers more readable. "Custom format" does not do what I want if I type that formatting.

Thanks in advance for any help!

Saludos from sunny Costa Rica,

Napoleon

[shg]

Try =CHOOSE(LEFT(A1,1) + 1, "", "", 2, 8, 2, 2, 2, 2, 8, 8) & A1

[napoelonguerra]

Hi "shg",

thanks for the try, but the formula you provided does not do anything anywhere. I've tried to copy it / type it / whatever it, but there is no reaction on the data.

Saludos,

Napoleon

[darkyam]

=if(or(left(a1,1)="3",left(a1,1)="8",left(a1,1)="9"),8,2)&a1
Note: for some reason, when I post this, it is putting spaces in the formula. You will need to remove these for it to work.

[Portuga]

In Spanish

=SI(O(IZQUIERDA(A1;1)="2";IZQUIERDA(A1;1)="4";IZQUIERDA(A1;1)="5";IZQUIERDA(A1;1)="6";IZQUIERDA(A1;1)="7");CONCATENAR(2;A1);SI(O(IZQUIERDA(A1;1)="3";IZQUIERDA(A1;1)="6";CONCATENAR(2;A1);(IZQUIERDA(A1;1)="9"));CONCATENAR(8;A1);""))

In English
=IF(OR(LEFT(A1;1)="2";LEFT(A1;1)="4";LEFT(A1;1)="5";LEFT(A1;1)="6";LEFT(A1;1)="7");CONCATENATE(2;A1);IF(OR(LEFT(A1;1)="3";LEFT(A1;1)="6";CONCATENATE(2;A1);(LEFT(A1;1)="9"));CONCATENATE(8;A1);""))
___________________________

TO MODERATORS:
Sometimes when posting (as is the case) Random spaces appear in the text. Specially bothering if we are talking formulas...

[napoelonguerra]

Hi All,

Thanks Portuga and Darkyam,

It sort of worked. I had the reedit a few numbers, but overall it did the trick. Most work was deleting many instances of "#VALUE!" where the formula encountered blank fields. On some international phone numbers it changed to XXXXX + E.11 or something.

...and now I'll see if those numbers can be imported into my cell-phone without too much trouble.

Anyway, thanks again for the help, and saludos from sunny Costa Rica,

Napoleon