Find String in another string - only between spaces

[Guest]

Hi,
I need to find and return a string in another string only if the searched
string is straight A to Z chars in a row.
saying A1:A3 is the string to find in B1:B3 is the formula.
need to find string "AB"
A1: ABDF_ABD
A2: ABDF_AB
A3: ABDF AB

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

 

[Domenic]

Does this help?

B1, copied down:

=IF(ISNUMBER(FIND(" AB "," "&SUBSTITUTE(A1,"_"," ")&" ")),"AB","NA")

 

[Guest]

LOL - I'm sure that probably made perfect sense to yuou when you wrote it,
but I'm just sat here scratching my head to be honest. I don't see any of the
strings containg AB BETWEEN spaces as per your subject line.

Also, just explain that A-Z characaters ina row for us as well please, just
so we are 100% on what you are after.

I had thought you were after for example, all instances where AB is in the
string and between spaces, but your example would seem to discount that
thought.

 

[Guest]

Ken,
I will extend my question.
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.

can you assist with?

TIA
Nir

 

[Domenic]

Have you tried the solution I offered?

 

[Ron Rosenfeld]

Have you tried the solution I offered?

It should work if the only separators are <space> and <underscore>.
--ron

 

[macropod]

hi Nir,

So what you want is to be able to find a test string:
a: comprising the whole of the target string,
b: followed by a space at the start of the target string,
c: following a space at the end of the target string,
d: with spaces both sides anywhere within the target string.
Correct?

Cheers

 

[Harlan Grove]

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.

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.

 

[Domenic]

Ah yes, thanks Ron! I missed the part about "being positioned between
signs", whatever that means...

 

[Ron Rosenfeld]

Ah yes, thanks Ron! I missed the part about "being positioned between
signs", whatever that means...

Well, I, too, have found the OP's description unclear. So I've just been
lurking


--ron