Search text for pattern and return numeric value - IF formula?

[gwstudent]

Suppose in column A I have text of various lengths like the following

... A 1: ...

... B 3: ...

... F 9: ...

... D 1: ...

... C 2: ...

... A 4: ...


In column B I would like to display the numeric value before the ":" only if I find A, B, or C prior to the numeric value. D and F would fail.


The following formula works fine when searching for A only but how do I get this to find A, B, or C? I've tried multiple IF variations but can't get this to work.

=MATCH(TRUE,INDEX(ISNUMBER(SEARCH("A "&{1,2,3,4,5,6,7,8,9}&":",A2)),),0)

 

[NARAYANK991]

Hi ,


I am not very sure about what you have explained , but can you try this ?


=IF(OR(MID(A2,FIND(":",A2)-3,1)={"A","B","C"}),VALUE(MID(A2,FIND(":",A2)-1,1)),"")


I assume that your text strings do not contain the colon ":" anywhere else other than in that one place , after a digit.


I also assume that there is only one digit , with a space character before it , and the letters A , B , C ,... before that.


Narayan

 

[Debraj]

Hi gwstudent,


Is VBA / UDF is acceptable!!! (You have already got one F1 solution)


Open Excel > Alt + F11 > Insert New Module.

paste the below code..

[pre]

Option Explicit

Function IsMATCH(S As String) As Boolean
IsMATCH = S Like "*[A-C] #:*"
End Function[/pre]

Now come back to Excel and write Formula as

=IsMATCH(A2)

It will return TRUE/FALSE, in case of match found.


Regards,

Deb

 

[gwstudent]

NARAYANK991 -


That's genius! Thanks so much

 

[3G]

Hi NARAYANK991 -


How could you increase the number of characters on either side? Say the list was:


Alpha 31:

Beta 1944:

Delta 323:


I understand you need to update the array of "A" "B" "C" to now include "D", but, say the numbers are now more complex.

 

[NARAYANK991]

Hi ,


Would the alpha characters always start from the left ?


Will the numeric values be separated from the alpha characters by a space character ?


Will the colon ":" be the right-most character in each string ?


Narayan

 

[3G]

Yeah...assuming all conditions are the same.

 

[Sajan]

Hi 3G,

You could use the SEARCH function instead of MID in Narayan's formula to do what you are asking.

For example:

=IF(OR(ISNUMBER(SEARCH({"Alpha","Delta"},A1))),VALUE(SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND(" ",A1)), ":","")))


Or you could also try something like the following:

=LOOKUP(99999, VALUE(SUBSTITUTE(LEFT(A1,LEN(A1)-1), {"Alpha","Delta"}, "")))


where your source string is in A1.


You can incorporate error trapping as needed.


Cheers,

Sajan.