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

Does this help?

B1, copied down:

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

In article ,
Nir wrote:

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

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"Nir" wrote:

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

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

"Ken Wright" wrote:

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"Nir" wrote:

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

Have you tried the solution I offered?

In article ,
Nir wrote:

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

On Wed, 01 Nov 2006 17:03:21 -0500, Domenic wrote:

Have you tried the solution I offered?


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

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
--
macropod
[MVP - Microsoft Word]


"Nir" wrote in message
...
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

"Ken Wright" wrote:

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.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------
It's easier to beg forgiveness than ask permission :-)



"Nir" wrote:

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

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
="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuv wxyz"

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.

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

In article ,
Ron Rosenfeld wrote:

On Wed, 01 Nov 2006 17:03:21 -0500, Domenic wrote:

Have you tried the solution I offered?


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

On Wed, 01 Nov 2006 22:03:38 -0500, Domenic wrote:

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