1

The function below is what I use to handle contact information (email/phone numbers). If the entry is an email id (has text), it will keep it as is. When a phone number is entered, it does a variety of things to it.

=IF(ISNUMBER(E2), IF(LEFT(E2, 2) = "44", REPLACE(E2,1,2,"0"), CONCAT("0",E2)), E2)

I am trying to add the code/function below to the phone number before Google Sheets applies the above formula.

=REGEXREPLACE(TEXT(E2,"##############"),"\D","")

Basically, if cell E2 is a number, then I want to REGEXREPLACE that number first, and then perform the additional operations. I'm not sure how to join the two formulas.

You may have a look at the expected output, and test your formulas here: spreadsheet sample

1

IF statement consists of 3 parts. understanding how it works:

if (something is something, do this, if not - do this)

so in formula expression:

=IF(E2="something", "its ok, do nothing", "E2 isn't ""something"" do something")

things get confusing if you want to do more advanced things, however, 3-part rule always remains



solution 1:

solution 2:

  • cell B3:

=IF(AND(ISNUMBER(A3),LEFT(A3, 2) =  "44"), REPLACE(A3,1,2,"0"),
 IF(AND(ISNUMBER(A3),LEFT(A3, 2) <> "44"), CONCAT("0",A3),
 IF(AND(ISTEXT(A3),  LEFT(A3, 3) =  "+44"),REPLACE(REGEXREPLACE(TEXT(A3,"##############"),"\D",""),1,2,"0"),
 IF(AND(ISTEXT(A3),  LEFT(A3, 2) =  "07"), A3,
 IF(AND(ISTEXT(A3),  LEFT(A3, 1) =  "7"),  CONCAT("0",REGEXREPLACE(TEXT(A3,"##############"),"\D","")),
 IF(ISTEXT(A3),A3))))))



why it didn't work for you:
cells A7, A9 and A11 are in your sheet formatted as TEXT and not as NUMBER while your formula claimed =IF(ISNUMBER(...etc. so returning value was equal to the original/initial value.

  • I am aware of how to use a basic if statement. Did you read my post? It includes a correctly functioning nested IF statement, and the question was about something very specific.. – Niaz Ahamed Jul 19 '18 at 20:58
  • @NiazAhamed I did read it... the thing is it wasn't really clear what exactly you want to accomplish due to lack of examples as well as combining two different things into one. now when I saw you sheet I can tell where is the issue – user0 Jul 19 '18 at 23:08
1

So, if E2 is a number, you want to run REGEXREPLACE(TEXT(E2,"##############"),"\D","") on that number, and then do IF(LEFT(value, 2) = "44", REPLACE(value,1,2,"0"), CONCAT("0",value)) on the result of the REGEXREPLACE.

In this case the formula you are after would be a lot neater with a helper cell to hold the intermediate result of the REGEXREPLACE, but it can be done without. You'll have to use the REGEXREPLACE formula instead of a plain E2 reference for each of the three times you refer to E2 in the inner IF, like so:

=IF(ISNUMBER(E2), IF(LEFT(REGEXREPLACE(TEXT(E2,"##############"),"\D",""), 2) = "44", REPLACE(REGEXREPLACE(TEXT(E2,"##############"),"\D",""),1,2,"0"), CONCAT("0",REGEXREPLACE(TEXT(E2,"##############"),"\D",""))), E2)

Updated Formula

Apologies, I thought that the initial ISNUMBER condition was already working for you. Now that I've seen your examples of how it should work, I've updated the formula:

=IF(value(REGEXREPLACE(TEXT(D3,"##############"),"\D",""))<>0, IF(LEFT(REGEXREPLACE(TEXT(D3,"##############"),"\D",""), 2) = "44", REPLACE(REGEXREPLACE(TEXT(D3,"##############"),"\D",""),1,2,"0"), CONCAT("0",REGEXREPLACE(TEXT(D3,"##############"),"\D",""))), D3)

This works on all the examples in your test sheet.

  • You are correct in your understanding of what I am trying to do, but your formula doesn't work.. You can try it on this test sheet I created: docs.google.com/spreadsheets/d/… – Niaz Ahamed Jul 19 '18 at 20:56
  • @NiazAhamed I've updated the answer with a revised formula – exterrestris Jul 20 '18 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.