Finding a text string within a cell

[Beverly]

This seems like a no-brainer, but I haven't figured it out. I want to test a certain cell to see if it contains the words "JR". There are other words in that same cell

Thanks in advance

-Beverly

 

[Peo Sjoblom]

One way

=ISNUMBER(FIND("JR",A1))

--

Regards,

Peo Sjoblom

This seems like a no-brainer, but I haven't figured it out. I want to

test a certain cell to see if it contains the words "JR". There are other
words in that same cell.

 

[Ken Wright]

=COUNTIF(A1,"*JR*") - Not case sensitive

=NOT(ISERROR(SEARCH("JR",A1))) - Not case sensitive

=NOT(ISERROR(FIND("JR",A1))) - Case sensitive

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

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

This seems like a no-brainer, but I haven't figured it out. I want to test a

certain cell to see if it contains the words "JR". There are other words in
that same cell.

 

[Harlan Grove]

One way

=ISNUMBER(FIND("JR",A1))

Another, also using two function calls but only one nesting level,

=(FIND("JR",A1&"JR")<LEN(A1))

 

[David McRitchie]

I don't see how any of the answers identified a word, they
all identified a string. The other words that might exist
might prove more interesting if they are "I", "II", "III", "IV", "V".
Such questions always come down to knowing your data,
including punctuation and placement.
i.e. Sr Juan Lopez
Harvey Brown, Sr
Not to mention that people have names that look like titles/rank,
but as far as I know they don't abbreviate them.

 

[Ken Wright]

LOL - I only really looked at the subject title Dave, but I take your point

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

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

I don't see how any of the answers identified a word, they
all identified a string. The other words that might exist
might prove more interesting if they are "I", "II", "III", "IV", "V".
Such questions always come down to knowing your data,
including punctuation and placement.
i.e. Sr Juan Lopez
Harvey Brown, Sr
Not to mention that people have names that look like titles/rank,
but as far as I know they don't abbreviate them.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[David McRitchie]

That's probably the difference, I never pay any attention to what
was in the subject once I start to answer a question. I had a manager
that nobody could read his email until we finally realized that the
first sentence is supposed to be a continuation of the subject.

LOL - I only really looked at the subject title Dave, but I take your point

I don't see how any of the answers identified a word, they
all identified a string. The other words that might exist [clipped]

 

[Ken Wright]

LOL - I'm also not consistent either. I was answering one question, over in
tek-Tips I think, and i went down one road whilst others went in another
direction. One of them then queried why I hadn't been specific about the answer
I gave and I gave some answer about not knowing what the OP was really after, to
which he replied - 'Isn't it what was in the title', which had I realised, gave
the whole damn question a new meaning - Doh!!!

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

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

That's probably the difference, I never pay any attention to what
was in the subject once I start to answer a question. I had a manager
that nobody could read his email until we finally realized that the
first sentence is supposed to be a continuation of the subject.

LOL - I only really looked at the subject title Dave, but I take your point

I don't see how any of the answers identified a word, they
all identified a string. The other words that might exist [clipped]