If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Finding a text string within a cell
One way
=ISNUMBER(FIND("JR",A1)) -- Regards, Peo Sjoblom "Beverly" wrote in message ... 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 |
#2
|
|||
|
|||
Finding a text string within a cell
=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 :-) ---------------------------------------------------------------------------- "Beverly" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.572 / Virus Database: 362 - Release Date: 27/01/2004 |
#3
|
|||
|
|||
Finding a text string within a cell
"Peo Sjoblom" wrote...
One way =ISNUMBER(FIND("JR",A1)) Another, also using two function calls but only one nesting level, =(FIND("JR",A1&"JR")LEN(A1)) -- To top-post is human, to bottom-post and snip is sublime. |
#4
|
|||
|
|||
Finding a text string within a cell
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 |
#5
|
|||
|
|||
Finding a text string within a cell
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 :-) ---------------------------------------------------------------------------- "David McRitchie" wrote in message ... 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.588 / Virus Database: 372 - Release Date: 13/02/2004 |
#6
|
|||
|
|||
Finding a text string within a cell
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. "Ken Wright" wrote ... LOL - I only really looked at the subject title Dave, but I take your point :-) "David McRitchie" wrote... I don't see how any of the answers identified a word, they all identified a string. The other words that might exist [clipped] |
#7
|
|||
|
|||
Finding a text string within a cell
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 :-) ---------------------------------------------------------------------------- "David McRitchie" wrote in message ... 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. "Ken Wright" wrote ... LOL - I only really looked at the subject title Dave, but I take your point :-) "David McRitchie" wrote... I don't see how any of the answers identified a word, they all identified a string. The other words that might exist [clipped] --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.588 / Virus Database: 372 - Release Date: 13/02/2004 |
Thread Tools | |
Display Modes | |
|
|