CODEWITHSUNDEEP
excel-formulagoogle-sheets-formula
Eyyy
Eyyy

Reputation: 129

Remove specific character from a cell depending on the cell value

Can someone help on this? I have column A with telephone number and Column B with country code. I want to remove the country code in column A if it is there to avoid duplication. Can someone help me?

Column A 1234567
Column B 1
Column C 1234567

Column C should have the formula to check if 1 is already in the beginning of the cell value A, if yes, it'll remove it, but if not, column B value will be added to it. Another scenario is below: Column A 234567 Column B 1 Column C 1234567

Appreciate your answer!

Upvotes: 0

Views: 326

Answers (1)

Tom Melly
Tom Melly

Reputation: 373

Something like this should do it:

=IF(TEXT(B1,"0") = LEFT(TEXT(A1,"0"), LEN(B1)),A1,CONCATENATE(B1,A1))

The logic is fairly straight-forward - take as many characters as the length of your country-code from the full number, and see if they equal the country code. If they do, ignore the country code, and just display the number; otherwise concatenate the two values.

Not sure why 'text' is needed, but fair enough I guess.

Upvotes: 1

Related Questions