• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to look for a text in a string

M

Mr.Karr

Member
Hi

I have a series of description in which I need to extract particular text. Please see the attached file.

Table:
MasterData - description or data dump
Value to look - table where you can find the actual text to look for
Value - formula needed to extract
 

Attachments

  • Extract.xlsm
    9.2 KB · Views: 21
Hi Karr,

It is not clear as to what exactly you are looking for. for every line in masterdata are you saying the formula to check if any of the listed "value to look" exists, if yes, get that word in the "Value" column?

Pls clarify.

regards,
Prasad DN
 
Hi prasaddn,
Alright, say, a table called 'Value to look', we need to look those values in the 'MasterData' column and paste it under 'Value' column.

Do you require any additional info.?
 
@Somendra Misra, I hope you're doing great there. A quick question;
In case, if I want to extract all the available strings/texts by entering above formula in cellA1 to A4. How can I ignore cellA value and pull next available sting in A2 ?

Please let me know if you need additional details
 
Mr.Karr

My contribution I have helped


=IF(ISERR(IF(LOOKUP(2^15,FIND($G$11:$G$14,A2),$G$11:$G$14),"Yes","No")),"Yes","No")

Decio in Brazil using Google Translator
 
@deciog, thank you for trying. Pls note, the formula which you've provided returns true or false but to get the strings/texts is the requirement.
 
Mr.Karr said:
@Somendra Misra, I hope you're doing great there. A quick question;
In case, if I want to extract all the available strings/texts by entering above formula in cellA1 to A4. How can I ignore cellA value and pull next available sting in A2 ?

Please let me know if you need additional details
Click to expand...

Can you explain in details please?
 
@Asheesh, thanks for pitching in. The problem statement is;
1. I have a list of keywords
2. I have a description/customer comments
3. Need a formula to compare comments cell with the keywords list and count the total number of available matching strings/texts

For example:
List:
Apple
Orange
Mango
Pineapple

Comment field: Went to market and bought Apple, Orange and mango.

Answer: 3

ps: @Somendra Misra helped with a formula as below however I'm looking for a formula which counts the exact match.

Code: 
=SUMPRODUCT(--ISNUMBER(SEARCH(EA!$H$3:$H$1524,Q10)))

as per the above formula, it returns 3 which is incorrect.
 
Mr.Karr, the example spreadsheet that had put as should be the answer to not have doubts, as in the formula I spent getting the "Yes", put number 1 and the "No" zero number, then add the column shows 3 results.

Decio in Brazil excuse, using Google Translate, and do not speak or write in Igles.
 
@deciog, let us not confuse the concept. Though I put 1 against Yes and 0 as No, it will not provide the desired result. My requirement is simple as below;

1. Find the matching keywords out of comments(matching keywords)
2. Count and show as output
 
Mr.Karr,Sorry to insistently, but want to help, look at the attached spreadsheet, if not for this, then displays the spreadsheet should look like

Decio in Brazil excuse, using Google Translate, and do not speak or write in Igles.
 

Attachments

  • Extract Decio.xlsm
    9.9 KB · Views: 2
Mr.Karr, now it was much better, I'll look for a solution, however have to leave, but come back tomorrow.

Decio in Brazil excuse, using Google Translate, and do not speak or write in Igles.
 
Hi,

Does the below formula answer your query...see the attached as well for ref..

COUNT(MATCH($A$2:$A$6,TRIM(MID(SUBSTITUTE(SUBSTITUTE(D2,",","")," ",REPT(" ",LEN(D2))),1+(ROW(INDIRECT("1:"&LEN(SUBSTITUTE(D2,",",""))-LEN(SUBSTITUTE(SUBSTITUTE(D2,",","")," ",""))+1))-1)*LEN(D2),LEN(D2))),0))

To be acknowledged as Ctrl + Shift + Enter..

But I have a question, are you looking for the unique count of matching sub strings..or the total count
 

Attachments

  • Query_Karr.xlsx
    11.7 KB · Views: 7
Last edited:
Try,

In E2, formula copy down and across :

=SUMPRODUCT(0+ISNUMBER(SEARCH(A$2:A$6&" ",SUBSTITUTE($D2&",",","," "))))

or,

=SUMPRODUCT(0+ISNUMBER(SEARCH(A$2:A$6&",",SUBSTITUTE($D2," ",",")&",")))

Regards
Bosco
 

Attachments

  • SearchAnimal.xlsx
    11.6 KB · Views: 8
bosco yip, I enjoyed, excellent solution.
Thank you for helping Mr.Karr

Decio in Brazil using Google Translator
 
@bosco_yip, please recheck the attached file.
I have encircled it as it shows 1 against mail but I do not have the keyword 'mail nowhere in the list. Please help.
 

Attachments

  • SearchAnimal.xlsx
    81.4 KB · Views: 4
You must log in or register to reply here.
Back
Top