CODEWITHSUNDEEP
excelexcel-formulaformula
Streching my competence
Streching my competence

Reputation: 381

Excel: Find words of certain length in string?

I have this file where I want to make a conditional check for any cell that contains the letter combination "_SOL", or where the string is followed by any numeric character like "_SOL1524", and stop looking after that. So I don't want matches for "_SOLUTION" or "_SOLothercharactersthannumeric".

So when I use the following formula, I also get results for words like "_SOLUTION":

=IF(ISNUMBER(FIND("_SOL",A1))=TRUE,"Yay","")

How can I avoid this, and only get matches if the match is "_SOL" or "_SOLnumericvalue" (one numeric character)

Clarification: The whole strings may be "Blabla_SOL_BLABLA", "Blabla_SOLUTION_BLABLA" or "Blabla_SOL1524_BLABLA"

Upvotes: 0

Views: 1615

Answers (2)

JvdV
JvdV

Reputation: 75840

Here is an alternative way to check if your string contains SOL followed by either nothing or any numeric value up to any characters after SOL:

=IF(COUNT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","1</s><s>")&"</s></t>","//s[substring-after(.,'SOL')*0=0]")>0),"Yey","Nay")

Just to use in an unfortunate event where you would encounter SOL1TEXT for example. Or, maybe saver (in case you have text like AEROSOL):

=IF(COUNT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","</s><s>")&"</s></t>","//s[translate(.,'1234567890','')='SOL']")>0),"Yey","Nay")

And to prevent that you have text like 123SOL123 you could even do:

=IF(COUNT(FILTERXML("<t><s>"&SUBSTITUTE(A1,"_","1</s><s>")&"</s></t>","//s[starts-with(., 'SOL') and substring(., 4)*0=0]")>0),"Yey","Nay")

Upvotes: 1

SJR
SJR

Reputation: 23081

Maybe this, which will check if the character after "_SOL" is numeric.

=IF(ISNUMBER(VALUE(MID(A1,FIND("_SOL",A1)+4,1))),"Yay","")

Or, as per OP's request and suggestion, to include the possibility of an underscore after "SOL"

=IF(OR(ISNUMBER(VALUE(MID(A1,FIND("_SOL",A1)+4,1))),ISNUMBER(FIND("_SOL_",A1))),"Yay","")

Upvotes: 2

Related Questions