# How to perform multiple operations where the IF statement is true?

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

`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:

• cell B3: `=REGEXREPLACE(TEXT(A3,"##############"),"\D","")`
• cell C3: `=IF(B3="",A3, IF(LEFT(B3, 2) = "44", REPLACE(B3,1,2,"0"), CONCAT("0",B3)))`
• hide column B

# 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

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