asked on

Search for text in a string

Could an Expert provide the answer to this please. I need to search a string of text and find one of several words.

i.e. I need to find Jan or Feb or Mar or Apr

I have the below formula but cannot figure out how to expand this to look for all of the above.

=IF(ISERROR(SEARCH("Jan",A1)),"","Jan")

Thanks

Jacques Geday

Try this
=IFERROR(MID("JanFebMarApr",FIND(A1,"JanFebMarApr",1),3),"")

gowflow

Jagwarman

ASKER

Hi gowflow I get a blank in the cell and in the formula window #Value

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Jagwarman

ASKER

Thanks teylyn that is brilliant.

I cant "accept" on this PC will do that later from home.

Jacques Geday

Sorry small mistake from my part
=IF(A1<>"",IFERROR(MID("JanFebMarApr",SEARCH(A1,"JanFebMarApr",1),3),""),"")

gowflow

Jagwarman

ASKER

It's still the same gowflow but the solution from teylyn works for me but thanks for trying

Jacques Geday

ok fine but I don't understand what is the problem now
gowflow

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

gowflow, can you explain your approach? I think you are searching in the wrong direction. As I understand it, cell A1 has some text, and it may contain any one of the month names in question, among other text. So Find() or Search() need to look in A1 for any of the values Jan, Feb, Mar, Apr, and if found, return the respective text string.

Your formula takes the value of A1 and tries to find it in the sting "JanFebMarApr" and then returns the respective text string. But if A1 contains only the text, we get the same result with

=A1

cheers, teylyn

Jacques Geday

Teylyn
As always you are great. I totally missed the boat here !!! tks for clarifying and sorry for Jagwarman for having you frustrated with my off-solution !

gowflow

krishnakrkc

For 12 months

=IFERROR(INDEX({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},MATCH(A1,{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},0)),"")

Kris

krishnakrkc

Please disregard my post

krishnakrkc

I midread the question earlier. Here is a revised one.

=IFERROR(LOOKUP(9.999E+307,SEARCH({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},A1),{"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"}),"")

Kris