Google Sheets – How to Perform Multiple Operations with IF Statement

concatenateformulasgoogle sheetsregexworksheet-function

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

Best Answer

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
see: https://docs.google.com/spreadsheets/d/sheet1

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))))))

see: https://docs.google.com/spreadsheets/d/sheet2

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.

Related Solutions

Google Sheets – How to Convert Jagged Array to 2D Array

For the OP example try the following formula:

=ArrayFormula(
IFERROR(
REGEXEXTRACT(","&
TRANSPOSE(TRIM(SPLIT(QUERY(TRANSPOSE(QUERY(
IFERROR(A4:A6&","&
REGEXEXTRACT(","&
SUBSTITUTE(B4:B6,", ",","),"^"&REPT(",+[^,]+",
COLUMN(OFFSET(A1,,,1,6))-1)&",+([^,]+)")&";")
,,1000))
,,1000),";"))),"^"&REPT(",+[^,]+",
COLUMN(OFFSET(A1,,,1,2))-1)&",+([^,]+)"
)
)
)

For the 30,000 rows:

Change the references accordingly.
Replace 1000 by 30000 or a larger number.
- The 1000 is the headers argument of QUERY(data,query,[headers]). We want use a number big enough to include all the rows of the source data.
If the second column of the source data has rows with more than 6 items, replace the 6 in OFFSET(A1,,,1,6) by the corresponding number of items.

See the reference for further details.

References

JOINing code-points after CHAR conversion in Google Spreadsheets

Google-sheets – Else statement is executing when TRUE

Regarding your second code, bear in mind that on Google Apps Script/JavaScript a single equal sign (=) is used to assign a literal or an objecto to a variable, so the syntax of the comparison on following code line is wrong

if(sheetname = "Template") {

The correct syntax is

if(sheetname === "Template") {

Notes:

On Google Apps Script / JavaScript:

=== means strict equality comparison
== means abstract equality comparison

Regarding the first code you should put the else keyword on the same like as the if closing bracket } because that is the right syntax and because the automatic semi-colon insertion,

Reference