Home > Software engineering >  Removing first two digits in Excel if the character length is greater than certain number
Removing first two digits in Excel if the character length is greater than certain number

Time:10-14

I have cell phone numbers in Excel some with country code- 91 and some without country code. I need to remove the country code. We have 10 digit phone numbers so I need to remove the first two digits if the character length of the cell is greater than 10, i.e. if I have a number with country code like 917465785236 I need to remove the first two digits- 91 so that I only have 7465785236. I am trying the below piece but it doesn't check the IF condition and removes the first two digits from all the cells. Can someone tell me what's wrong I am doing here:

=IF((LEN(A1>10)),RIGHT(A1, LEN(A1)-2))

CodePudding user response:

You probably need to put the parentheses differently for the Len function:

=IF((LEN(A1)>10),RIGHT(A1, LEN(A1)-2))

CodePudding user response:

You're not using the parenthesis properly. Also since you strictly want to have 10 characters, you don't need to calculated the length in the RIGHT formula.. It needs to be like this:

=IF(LEN(A1)>10,RIGHT(A1, LEN(A1)-2),A1)

Now, that is the issue with your formula, but the solution to your question doesn't even need a IF statement, You can simply use:\

RIGHT(A1,10)

It will automatically get the 10 characters at the end and remove the rest.

  • Related