In Excel how to search a text string + get TRUE/FALSE result

Hi,

With Ms Excel I want to search a text string within an another text string AND get a TRUE/FALSE result in return. I want to find if 'EUR' is present in the searched text string and if yes, apply a certain rate to convert the numerical amount located in another field to USD.

I tried using the SEARCH function, but it looks like it is only able to return the position of the searched text string withing the other text string... And I can't find any other function that can do this directly or indirectly.

Would anyone know how to do this?

Thanks!
Awale

Welcome to the forum. This one is pretty simple.

Say you have cell A1 as "asdfEURasdf".

To evaluate if that cell contains EUR and return "TRUE" you would use this formula:

=if(FIND("EUR",A1),"TRUE","FALSE")

That's it, enjoy!

Thanks a lot for your quick response! Most appreciated

I tried that and I couldn't get to the FALSE when "EUR" was not in the text cell I was exploring (it worked fine when "EUR" was found though).

Starting from there I found (almost by chance...) in the Excel help that IF(ISNUMBER(FIND("EUR",A1),"TRUE","FALSE")) did work in both cases (TRUE or FALSE).

So I'm a happy man

What do you think of that? Is it my version of Excel (2003) who requires the ISNUMBER?

There are two ways:

(1) =NOT(ISERROR(SEARCH(A1,B1))) where cell A1 contains the text you are searching for, e.g. in your case "Eur" and cell B1 contains the text you are searching. I've used this function in Excel 2003 for years and Excel 2010 recently

(2) =ISNUMBER(FIND(A1,B1)) where cell A1 contains the text you are searching for and cell B1 contains the text you are searching. I've tried this function in excel 2010 and it works fine. Havn't tried it in Excel 2003 so cannot comment there

Both functions do what you asked: return "True" when the sought after text is found and "False" when the sought after text is not found. In my opinion the better algorithm is the 2nd because it requires fewer text entries. However cannot comment as to which function computes faster, which is important if you are searching a database with many records. In both cases it is not necessary to bundle this inside an "IF" statement since the functions return "True" or "False" naturally. :painting:

Hi - I am looking for a variation on this solution.

I have a column containing several hundred text strings, and I want to be able to count occurrences of a specific sub-string.

so for example if i have a column (A) that each row in that colum contains 5 or 6 word sentences. I want to get a count of all the occurrences of the word 'apple' that occur in that column.

Thanks in advance!

The variation is quite simple. Use an "IF" statement in a column next to each occurrence of the text string to generate a "1" if the string you are looking for is found in the cell to the left, and either a "" (space) or "0" if the string you are looking for is not found in the cell to the left. An example follows:

Put this statement in column B1 with the first occurrence of the string you are searching is in A1: =IF(ISNUMBER(FIND(A1,"String you are looking for")), 1,0). Copy this statement down so it is next to each string statement you are searching, i.e., 100 times if you are searching one hundred strings. The formula will always look in the cell to the left. If the string you are searching is in A5, A50, A100 then B5, B50 and B100 will have a 1 and all the rest of the columns with have a 0. You can create a sum statement for column B to count the 1's or simply a sum of the B column and use the Auto-Filter function if you have a header column to see all the 1's in column B and therefore look at the occurrences of your string in column A. If you want to search for multiple string statements you can put them in say C1 and use the $ sign to lock it that cell reference. For example: =IF(ISNUMBER(FIND(A1,C$1)), 1,0) with the statement: The string you are looking for (no quotes no equal sign) in C1.

I tried the formula above it it works great for my needs... I modified it slightly to =IF(FIND(Sheet1!$A3,$C2),Sheet1!B3,"Unknown Group"),

Now I think I need to turn this into an array...... Here is my scenario... I have a list of Incident tickets (currently 106)... and 76 Key Words (Sheet 1 Column A) that can be found in the description of the Incidents (Column "C")... What i need to do is search the description for EACH of the Key Words if it finds a match ("There can be only one")... tell me the Assignment group value in Sheet1! Column B... Can you assist me?

Thanks SO MUCH.... April.

I have a page of text generated by a script. I would like to loop and search a column of cells line by line for a word returning either true or false.

I have tried using search, match and find but can not put together something that works.

Thankyou in advance

Try this:

There are two ways:

(1) =NOT(ISERROR(SEARCH(A1,"Word"))) where cell A1 contains the text line you are searching for a specific word, e.g. someone's name, and "Word" contains the spedific word you are searching for, i.e., the name or other word you are looking for. Copy this function down for the number of lines of text you actually have. So if you have 100 lines of text copy the function down 100 lines. Each line of text will have a row number corresponding to the row for the page of text being searched. So the last row of text will have: =NOT(ISERROR(SEARCH(A100,"Word"))) I've used this function in Excel 2003 for years and Excel 2010 recently

(2) =ISNUMBER(FIND(A1,"Word")) where cell A1 contains the text line you are searching for a specific word, e.g. someone's name, and "Word" contains the spedific word you are searching for, i.e., the name or other word you are looking for. Copy this function down for the number of lines of text you actually have. So if you have 100 lines of text copy the function down 100 lines. Each line of text will have a row number corresponding to the row for the page of text being searched. So the last row of text will have: =ISNUMBER(FIND(A100,"Word")) I've tried this function in excel 2010 and it works fine. Havn't tried it in Excel 2003 so cannot comment there

Both functions do what you asked: return "True" when the sought after text is found and "False" when the sought after text is not found. In my opinion the better algorithm is the 2nd because it requires fewer text entries. However cannot comment as to which function computes faster, which is important if you are searching a database with many records. In both cases it is not necessary to bundle this inside an "IF" statement since the functions return "True" or "False" naturally.

Thankyou for both functions, however I was hoping I could put it in a looping macro. For two reasons really;
- the rest of the sheet data is constructed using a macro and sitting it in the macro suits
- I find macro's preserves the excel file size.

But thankyou again for your reply

Regards

Wayn0i

Hi Wayn0i,
Try the following macro:


Private Sub FindingString()

'Searches Column "A" for the word "hat" (Not case sensitive)
'To make it case sensitive, change the last part of the InStr function to vbBinaryCompare
'Puts the result in Column B (Found / Not Found)

Dim lngI As Long

For lngI = 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
If InStr(1, Range("A" & lngI).Value, "hat", vbTextCompare) Then
Range("B" & lngI).Value = "Found"
Else
Range("B" & lngI).Value = "Not Found"
End If
Next lngI

End Sub



Hope it helps!
-Mike

I have 2 columns of values that are CUSIP numbers with some as repeats. I need a formula that will tell me if the string of characters in A1 matches a string of characters in column B then return a true value otherwise false. Then the same for A2 and so forth. Anyone have any ideas?

Hi, if you're looking for exact matches, you can use a VLookup formula. Say you have 3 columns: Column A has the value you want to lookup to see if there are any matches. Column B is the list of values. Column C will have the formula.

Formula in cell C2:
=IF(ISERROR(VLOOKUP(A2,B:B,1,0)),"Not Found", "Found")


-Mike

I am trying to do the following.

suppose A1 has the the following sentences:

4-march-2012: Rent Reminder received
20 Mar 12 - Calculation done for next month
2 Apr 12 - All Reminder for collection sent

I want to find if the string "Reminder for collection" is present in the cell A1 so that i can carry on with fruther steps.

I am using the following -
=IF(ISNUMBER(SEARCH("Reminder for collection",A1)),IF(MID(A1,SEARCH("Reminder",A1),LEN("Reminder for collection"))="Reminder for collection","Reminder sent","Reminder not sent"), "Reminder not sent")

but it doesnot work (output is "Reminder not sent" instead of "Reminder sent") as there are two sentences containing the word "Reminder"

The sentences needs to be in the same cell
there will be sentences containing the same word "Reminder"
Please help.

Hi, what if you tried this formula instead:

=IF(ISERROR(FIND("Reminder for collection ",A1)),"Not found", "Found")


-Mike