Phone correct Line Up Format.

[anu0512]

Hi,

I have phone number database and the problem the number in the sheet are not in proper sequence or prefix, some number 055668878, 009715585879. So what logic I use to pull the number which start from 055668875 and remove the zero in starting and add 971. Same with number starting from 009715585879 so remove the zero only. I want all the phone number should be set in the order like 971556465874. I have attach the test excel sheet.

[shukla.ankur281190]

Try

B2=IF(LEFT(A2,1)="9",A2,IF(LEFT(A2,3)+0=9,RIGHT(A2,LEN(A2)-FIND(0,A2)-1),IF(LEFT(A2,1)="0","971"&RIGHT(A2,LEN(A2)-FIND(0,A2)))))

Please Login or Register  to view this content.

[sourabhg98]

Hi,
Use this in C2 and copy down
=IF(LEN(A2)=10,"971"&RIGHT(A2,9),IF(LEN(A2)=12,A2,IF(LEN(A2=14),RIGHT(A2,12))))
Check attached-

[FlameRetired]

Another one.

Please Login or Register  to view this content.

[anu0512]

Another one.

Please Login or Register  to view this content.

Thank you very much for the help, I solve the query with help of you guys. I just copy paste. Falme can you explain the logic you use ?

[FlameRetired]

The first part =IF(LEFT(A2,5)="00971",MID(A2,3,99),IF(LEFT(A2,2)="05",971&MID(A2,2,99),A2)) tests the value to see if the 5 LEFTmost characters

are "00971"; if they are the MID function, starting at character position 3, returns the balance of the string. 99 is an arbitrarily large number that is likely to go

beyond the end of the string.

The second part =IF(LEFT(A2,5)="00971",MID(A2,3,99),IF(LEFT(A2,2)="05",971&MID(A2,2,99),A2)) says that if it is not "00971" then test if the

2 LEFTmost characters are "05"; if they are then MID starts at character position 2 and behaves as in the first part. If neither condition is true then the string

must be OK, so it returns the original string =IF(LEFT(A2,5)="00971",MID(A2,3,99),IF(LEFT(A2,2)="05",971&MID(A2,2,99),A2)).

Does this help?

[anu0512]

The first part =IF(LEFT(A2,5)="00971",MID(A2,3,99),IF(LEFT(A2,2)="05",971&MID(A2,2,99),A2)) tests the value to see if the 5 LEFTmost characters

are "00971"; if they are the MID function, starting at character position 3, returns the balance of the string. 99 is an arbitrarily large number that is likely to go

beyond the end of the string.

The second part =IF(LEFT(A2,5)="00971",MID(A2,3,99),IF(LEFT(A2,2)="05",971&MID(A2,2,99),A2)) says that if it is not "00971" then test if the

2 LEFTmost characters are "05"; if they are then MID starts at character position 2 and behaves as in the first part. If neither condition is true then the string

must be OK, so it returns the original string =IF(LEFT(A2,5)="00971",MID(A2,3,99),IF(LEFT(A2,2)="05",971&MID(A2,2,99),A2)).

Does this help?

Sorry can you explain it because I'm new to excel, in step wise

[Pepe Le Mokko]

Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding.

[sourabhg98]

Hello,
Maybe I can explain my formula
This is the formula I used-

Please Login or Register  to view this content.

The "LEN" function which you see here returns the number of characters in a cell value.
So =LEN(A2) returns the number of characters present in a cell A2.

The "RIGHT" function returns a specified number of characters from the right.
So =RIGHT(A2,9) will give you nine characters from the right in cell A2.
So if A2 has 123abc456def then =Right(A2,9) will give you abc456def i.e. the nine characters from the Right.

So the formula above basically does this-

Please Login or Register  to view this content.

This checks whether number of characters in cell A2 is 10 or not.

Please Login or Register  to view this content.

If TRUE i.e. if there are 10 characters in cell A2 then the formula will show result "971" & RIGHT(A2,9). "&" << this thing combines both the texts together.
So if there is 0504663775 in A2 we can see it has 10 characters so the result will be
="971" & Right(A2,9)
="971" & Right(0504663775,9)
="971" & "504663775"
="971504663775"
Which is the answer we need.

Please Login or Register  to view this content.

So, if the characters in A2 are not 10 then the formula checks if the characters are 12 or not.

Please Login or Register  to view this content.

If there are 12 characters then the result is the value in cell A2 itself.

Please Login or Register  to view this content.

If the characters in cell are not 12 then the formula checks whether the number of characters are 14 or not.

Please Login or Register  to view this content.

So, if the number of characters are 14 the result is RIGHT(A2,12) which gives the 12 digits from the right in cell A2.

I hope this helps!!

[FlameRetired]

Sorry can you explain it because I'm new to excel, in step wise

The IF function is one that executes from left to right. It's syntax is basically this IF(<some test condition is TRUE>, <Then do this>, <Else do this>)

Formulae can be "nested" inside one another. This is such a formula.

LEFT(A2,5) returns the 5 leftmost characters of A2 in the first row. That address changes as

formula is copied downward (A3 the next time ....etc. etc.) IF those 5 characters are equal to 00971 then

the MID function (using A2) starting at character position 3 (where the 9 is) returns the balance of

the string. A happy property of the MID function is that it will allow numbers longer than the balance of

the string (A2 in this case) and return what it can without errors. Hence the use of 99 ... an arbitrarily

large number likely to be long enough to return the balance. The resulting strings here will always begin

with the pre-existing 971 followed by the balance of the string.


If those leftmost 5 characters do not = 00971 then another nested IF function tests to see if the

2 leftmost characters = 05. If they do then the MID function performs as before, but this time

starting at character position 2 (where 5 is) and returns the balance of A2 as before. It also pre-appends

the required "971" to that result. If that condition is also not true it returns the original value in A2

without doing anything to it.


Did that help?