Formatting Numbers - Excel

[ThomasBrown]

Hi there folks

I have an application and I can export the client details to excel and in a row I have all the telephone numbers. They have no spaces:

+27211234567
+27217896543
+27833698521
+27112587413

Now what I need to do is count the lenghth, if the length = 12 and starts with +27 then drop the +27 and replace with 0 and then format the numbers with spaces or (). I may need to format it in multiple differant formats which I could go in differant columns.

so it would look like this

+27211234567 021 123 4567 (021)410 5000 (021) 410-5000
+27217896543 021 789 6543 (021)789 6543 (021) 789-6543
+27833698521 083 369 8521 (083)369 8521 (083) 369-8521
+27112587413 011 258 7413 (011)258 7413 (011) 258 7413

If the number is not 12 characters in length nothing must happen to the number. It must remain as is.

I know I could do it in Crystal using the picture () formula, but how is this done in Excel.

Any help would be great.

Many Thanks
Thomas

 

[KenWright]

One way would be using formula like

=IF(AND(LEN(A1)=12,LEFT(A1,3)="+27"),"(0"&MID(A1,4,2)&") "&MID(A1,6,3)&"-"&RIGHT(A1,4),A1)

You also have formatting options that you can create with custom number formats that may be able to achieve some of this.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[ThomasBrown]

Thank You Ken,

That will work just perfectly

Cheers
Thomas

 

[KenWright]

Obviously just use the same principles if you want to build the string in the other example you gave.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

Thomas,

FYI

If we were to define NUMBERS as values upon which we can and will do arithmetic and DIGIT STRINGS as entities that look like numbers, but in reality they are identifiers that have nothing at all to do with their equivalent NUMERIC values....

THEN

things like a telephone "number" or an invoice "number" or a Zip Code is NOT really a NUMBER to be arithmetically massaged, but a DIGIT STRING identifier, with which STRING FUNCTIONS can be used.

The VALUES and SORT SEQUENCES are DIFFERENT.

It is a significant DISTINCTION that spreadsheet users should have in mind

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}