Is regular expression possible in IF formula

[Paresh Padhye]

Is it possible to use regular expression in IF formula

For e.g. col A has following values

ABC
AAA
ADE

then it is possible to write array formula something like {=if(A1:A3) = A*, "<<TRUE CONDITION>>", "<<FALSE CONDITION>>"}

 

[Somendra Misra]

Hi Paresh,

Welcome to the forum.

Try this array formula
=IF(ISNUMBER(SEARCH("A*",A1:A3)),True condition,False condition)

Regards,

 

[NARAYANK991]

Hi Padhye ,

The example given is not really a regular expression ; if it is representative of what you wish to do , you can use the LEFT function to isolate the left-most character in the data :

=IF(AND(LEFT(A1:A3,1)="A"),"A is the starting character in all 3 cells","A is not the starting character in all 3 cells")

=IF(OR(LEFT(A1:A3,1)="A"),"A is the starting character in at least 1 cell","A is not the starting character in any cell")

Both the above are array formulae , to be entered using CTRL SHIFT ENTER.

Narayan

 

[Somendra Misra]

@Paresh Padhye

Do you want a single output or an array output to be used in another formula? if you want single output than @NARAYANK991 sir formula is perfect, but if you want to return an array, use something like this.

=IF(IFERROR(SEARCH("A",A1:A3),0)=1,1,0)

Regards,

 

[Paresh Padhye]

I missed one condition in my query. I will elaborate more

Assume col A and B

ABC 5
AAA 10
ADE 15

I can use sumif with wild card for e.g. SUMIFS(B1:B3, A1:A3="*") but in case IF is used

something like - {=SUM(if(A1:A3) = *, B1:B3,0)}

Then formula does not interpret * as wildcard character but tries to find actual character "*" in A1:A3 range. Is there a way to male IF formula interpret * as wildcard character (i.e. any character repeated any amount of time)

(* You may think why I am using IF when SUMIF is available. Actually I want to use various formulas like MAX, MIN, SMALL, LARGE in place of SUM given above. I have simplified and then used above scenario as example)

 

[Somendra Misra]

@Paresh Padhye

I think its because you are using = sign in your if formula thats why it is find a * in A1:A3, whereas in sumif it is passed as an argument.

Regards,

 

[Debraj]

@Paresh Padhye

Simple equality relationship, which check equal or not, doesn't support Wildcard.
Instead try this.. (Array Formula)

=MAX(IF(ISNUMBER(SEARCH("A*",$A$1:$A$3)),$B$1:$B$3))

 

[Paresh Padhye]

Thank you very much NarayanK, Somendra, Debraj.

IF with SEARCH is accepting * as WILDCARD and has solved my problem

 

[NARAYANK991]

Hi Padhye ,

SEARCH needs to be used only when the character you are looking for is within a string ; looking for the left-most character using SEARCH is overkill ; the formula that Deb has posted uses ISNUMBER and SEARCH ; the same result is obtained using the formula :

=MAX(IF(LEFT($A$1:$A$3,1)="A",$B$1:$B$3))

Use SEARCH only if you really need to search i.e. you do not know the position of the character or substring you are looking for.

Narayan