Excel: Find words of certain length in string?

Question

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"

By "and stop looking after that", do you mean "ends in '_SOL'"? — Chronocidal, Jun 02 '20 at 08:39
If you are checking if the cell contents end in "_SOL", then `=IF(RIGHT(A1,4)="_SOL","Yay","")` should work for you. — Applecore, Jun 02 '20 at 08:45
@Chronocidal no, not neccesarily. These are in the mid of the text string. So either for example "Blabla_SOL_BLABLA" or "Blabla_SOLUTION_BLABLA" or "Blabla_SOL1524_BLABLA". — Streching my competence, Jun 02 '20 at 08:53
What is the terminating character going to be? Is it always "_"? — Applecore, Jun 02 '20 at 08:59
When its always `_SOL_`your are searching for, why dont you look for that isntead? — MGP, Jun 02 '20 at 09:02
@Applecore before the word yes, but after, it can be anything (whomever is behind my data is not very consistent in the naming conventions, and I won't be able to change that behavior). So far though, it does seem like the matches I want actually are the ones that are either `_SOL_` or `_SOL` with numeric values after the three letters SOL, like `_SOL1524`, and that I don't want matches that has other than numeric values after it). Maybe it's possible to specify numeric follow up? I updated the question to include numerics — Streching my competence, Jun 02 '20 at 09:07
@JvdV yes, so the check should preferably only be for 1 numeric character. See the solution from SJR below. — Streching my competence, Jun 02 '20 at 09:32

SJR · Accepted Answer · 2020-06-02T09:48:00.997

2

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

edited Jun 02 '20 at 09:48

answered Jun 02 '20 at 09:12

SJR

22,233
6
16
23

2

Might want to also add an `OR` for when it's followed by an underscore? – Chronocidal Jun 02 '20 at 09:13
@SJR nice! I added an `OR` condition and ended up with this, which works perfectly: `=IF(OR(ISNUMBER(VALUE(MID(A1,FIND("_SOL",A1)+4,1)))=TRUE,ISNUMBER(FIND("_SOL_",A1))=TRUE),"Yay","")`. Feel free to update the answer with it. I marked it as the solution as it got me to where I needed to be. – Streching my competence Jun 02 '20 at 09:31
Sorry, I missed that bit. Thanks for accepting - you can post your own adaptation as an answer if you wish. – SJR Jun 02 '20 at 09:46

JvdV · Answer 2 · 2020-06-02T10:26:38.733

1

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

edited Jun 02 '20 at 10:26

answered Jun 02 '20 at 10:01

JvdV

41,931
5
24
46

1

Really good! I'm still just getting into reading XML formulas, so I'm going with SJR's solution for now. Upvoted though. I'm sure it is good for anyone reaching this via search! Thanks (: – Streching my competence Jun 03 '20 at 11:48
1

It's not a problem at all @Strechingmycompetence. SJR's formula is perfectly fine (you should also upvote that if it's been helpfull). Just wanted to give you the option. Have a look [here](https://stackoverflow.com/q/61837696/9758194) if these formulas interest you. – JvdV Jun 03 '20 at 11:58

Excel: Find words of certain length in string?

2 Answers2