MS Excel

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsBusiness ApplicationsMS Excel

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rating: Thread Rating: 3 votes, 3.67 average. Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old August 10th, 2005, 01:51 PM
awale awale is offline
Contributing User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Aug 2005
Posts: 2 awale New User: is a brand new recruit and a unknown entity at this point. 
Time spent in forums: < 1 sec
Reputation Power: 0
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

Reply With Quote
  #2  
Old August 10th, 2005, 02:42 PM
kurt kurt is offline
Contributing User
Tutorialized Novice (500 - 999 posts)
 
Join Date: Jan 2004
Posts: 507 kurt New User: is a brand new recruit and a unknown entity at this point. 
Time spent in forums: 5 m 48 sec
Reputation Power: 0
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!

Reply With Quote
  #3  
Old August 10th, 2005, 04:49 PM
awale awale is offline
Contributing User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Aug 2005
Posts: 2 awale New User: is a brand new recruit and a unknown entity at this point. 
Time spent in forums: < 1 sec
Reputation Power: 0
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?

Reply With Quote
  #4  
Old October 17th, 2011, 12:26 PM
OldGuy OldGuy is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Oct 2011
Location: California
Posts: 3 OldGuy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 37 m 46 sec
Reputation Power: 0
Quote:
Originally Posted by awale
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


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:

Reply With Quote
  #5  
Old January 24th, 2012, 06:36 PM
John Machacek John Machacek is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Jan 2012
Posts: 1 John Machacek User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 m 23 sec
Reputation Power: 0
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!

Reply With Quote
  #6  
Old January 27th, 2012, 09:51 AM
OldGuy OldGuy is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Oct 2011
Location: California
Posts: 3 OldGuy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 37 m 46 sec
Reputation Power: 0
Reply to John Machacek

Quote:
Originally Posted by John Machacek
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.

Reply With Quote
  #7  
Old March 6th, 2012, 09:53 AM
apie apie is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Mar 2012
Posts: 1 apie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 20 m 48 sec
Reputation Power: 0
Facebook
In Excel how to search a text string - ARRAY

Quote:
Originally Posted by kurt
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!


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.

Reply With Quote
  #8  
Old March 26th, 2012, 06:09 AM
wayn0i wayn0i is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Mar 2012
Posts: 2 wayn0i User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 41 m 56 sec
Reputation Power: 0
Another variation please

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

Reply With Quote
  #9  
Old March 26th, 2012, 10:01 AM
OldGuy OldGuy is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Oct 2011
Location: California
Posts: 3 OldGuy User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 37 m 46 sec
Reputation Power: 0
Reply to Wayn0i on Page of Text

Quote:
Originally Posted by wayn0i
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.

Reply With Quote
  #10  
Old March 27th, 2012, 09:14 AM
wayn0i wayn0i is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Mar 2012
Posts: 2 wayn0i User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 41 m 56 sec
Reputation Power: 0
Cheers OldGuy

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

Reply With Quote
  #11  
Old March 31st, 2012, 04:19 AM
Mike382P Mike382P is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Jun 2011
Posts: 17 Mike382P User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 49 m 4 sec
Reputation Power: 0
Quote:
Originally Posted by wayn0i
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

Reply With Quote
  #12  
Old April 30th, 2012, 04:38 PM
atksu atksu is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Apr 2012
Posts: 1 atksu User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 m 2 sec
Reputation Power: 0
Looking up a string from table to table

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?

Reply With Quote
  #13  
Old April 30th, 2012, 05:50 PM
Mike382P Mike382P is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Jun 2011
Posts: 17 Mike382P User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 49 m 4 sec
Reputation Power: 0
Quote:
Originally Posted by atksu
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

Reply With Quote
  #14  
Old May 4th, 2012, 05:53 AM
nemuos nemuos is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: May 2012
Posts: 1 nemuos User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 m
Reputation Power: 0
How to searsh a string in multiple sentences in a cell

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.

Reply With Quote
  #15  
Old May 5th, 2012, 11:47 AM
Mike382P Mike382P is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Jun 2011
Posts: 17 Mike382P User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 49 m 4 sec
Reputation Power: 0
Quote:
Originally Posted by nemuos
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

Reply With Quote
Reply

Viewing: Tutorialized ForumsBusiness ApplicationsMS Excel > In Excel how to search a text string + get TRUE/FALSE result


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap