Re: Find String in another string - only between spaces

Nir wrote...
....
the idea is to find a string in other string only if it is a "perfect" word.
"perfect" word as it is idetentified in the file can be positioned in the
beginning or end of the string or between spaces or signs.

Still unclear.

Your original examples.

"Nir" wrote:
....
need to find string "AB"
A1: ABDF_ABD
A2: ABDF_AB
A3: ABDF AB

result should be B1 - NA, B2-AB , B3-AB

Do you mean you need to find the substring AB as long as the characters
on either side of it, if any, aren't letters? Or aren't letters or
numerals? First step would be finding all instances of the substring
sought (ss) in the string (s), then checking whether the characters
immediately before and after the substring sought are letters or other
'word' characters.

Easier with some common defined names like

seq
=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1024,1))

bc
="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"

Then try the array formula

=IF(COUNT(IF(MID(s,seq,LEN(ss))=ss,1/ISERROR(FIND(MID(" "&s,seq,1),bc))
/ISERROR(FIND(MID(s&" ",seq+LEN(ss),1),bc)))),ss,"NA")

to search for ss in s, and if found return ss if it's bracketed by
characters not in bc. Otherwise, return NA.

.

Relevant Pages