Hello,
Maybe I can explain my formula
This is the formula I used-
=IF(LEN(A2)=10,"971"&RIGHT(A2,9),IF(LEN(A2)=12,A2,IF(LEN(A2=14),RIGHT(A2,12))))
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-
=IF(LEN(A2)=10,"971"&RIGHT(A2,9),IF(LEN(A2)=12,A2,IF(LEN(A2=14),RIGHT(A2,12))))
This checks whether number of characters in cell A2 is 10 or not.
=IF(LEN(A2)=10,"971"&RIGHT(A2,9),IF(LEN(A2)=12,A2,IF(LEN(A2=14),RIGHT(A2,12))))
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.
=IF(LEN(A2)=10,"971"&RIGHT(A2,9),IF(LEN(A2)=12,A2,IF(LEN(A2=14),RIGHT(A2,12))))
So, if the characters in A2 are not 10 then the formula checks if the characters are 12 or not.
=IF(LEN(A2)=10,"971"&RIGHT(A2,9),IF(LEN(A2)=12,A2,IF(LEN(A2=14),RIGHT(A2,12))))
If there are 12 characters then the result is the value in cell A2 itself.
=IF(LEN(A2)=10,"971"&RIGHT(A2,9),IF(LEN(A2)=12,A2,IF(LEN(A2=14),RIGHT(A2,12))))
If the characters in cell are not 12 then the formula checks whether the number of characters are 14 or not.
=IF(LEN(A2)=10,"971"&RIGHT(A2,9),IF(LEN(A2)=12,A2,IF(LEN(A2=14),RIGHT(A2,12))))
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!!
Bookmarks