Find list of words from sheet2 in sheet1 before a comma and extract text vba

satish78

Board Regular
Joined
Aug 31, 2014
Messages
218
Hi Friends,

Trying to find the solution on my task. But did not find suitable one to the need.
Here is my query and sample file with details.

Need a formula to find list of words before a (,) comma from sheet2A:A in Sheet1A:A(there is no data range like A1:a10) and extract text before the match
Formula should out put results like shown in ColumnD
ColumnC has found words from sheet2A:A in sheet1A:A

 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
result from your example
text before the last comma and last keyword found in the list (sheet2)
fromlist.png

but unfortunately Power Query ? ?
 
Last edited:
Upvote 0
Your VBA does not do correct extraction.
That does not help me improve or change the code.

1. Did you take note of what I said when I provided the code?
(using the original list in column A of Sheet2 of the sample file that you provided)


2. Did you actually test my code in your workbook, or did you just look at my results in post 24?

3. Could you please give me an example that does not work? Please give me
- the column A text,
- the result that my code gives for you
- the 'word' that the code should be using in that example
- the desired result

Please respond to all 3 questions individually.
 
Upvote 0
@Peter_SSs
I can confirm your code works well and there is nothing to change
maybe op want column with the last keywords?
Thanks for the confirmation and yes, you may be right about listing the relevant word(s) as well. Hopefully we will get confirmation or clarification.
 
Upvote 0
That does not help me improve or change the code.

1. Did you take note of what I said when I provided the code?



2. Did you actually test my code in your workbook, or did you just look at my results in post 24?

3. Could you please give me an example that does not work? Please give me
- the column A text,
- the result that my code gives for you
- the 'word' that the code should be using in that example
- the desired result

Please respond to all 3 questions individually.
I did try with VBA, So, Got error at first, I took a look on library enabled or not. So, I have enabled library now. Its working now.

Thanks for helping me in putting efforts to solve this query.
Credit goes to Peter and to all team helping me in solving this query.

Thanks again.
Satish
 
Upvote 0
Edit: Not sure this adds anything - I'd failed to read the complete thread. The perserverance of everyone is impressive.


in your original example you appear to have extracted all the words before the word that appears in Column C, there is no correlation with the commas that exist in the text. If you simply want to extract all the words before the final comma then try entering this into A2 and copying down:

=LEFT(SUBSTITUTE(A2,",","¬",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))),FIND("¬",SUBSTITUTE(A2,",","¬",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)

it looks horrid, but firstly calculates the number of commas in the text, replaces the last one with a "¬" symbol and finally takes everything to the left of the "¬".

HTH
 
Upvote 0
Can it also be possible to extract words between 2 words from list of 2 range columns?

For example,

In sheet1A:A data.
In sheet2A:A List of words (Range 1)
In sheet2B:B List of words (Range 2)


I need VBA to pick words from sheet2 both ranges and lookup in sheet1 data and extract all words(connected words) into sheet1B:B while separating with " # "(includes space).
If there are words with comma separated extract into ColumnC:C (shown below)

Example sentence data in Sheet1A:A

Decipher information about medication dosage, administration, and conditions of
planning process that gathered data about critical business issues, assessed the competitive landscape, gained

I need vba to pull like this "decipher information" in sheet1B:B
and ("mmedication dosage" # "administration" # "conditions of") in sheet1C:C

"planning process" # "gathered data" in sheet1B:B
"critical business issues" # assessed # "competitive landscape") in sheet1C:C
 
Upvote 0
Can it also be possible to extract words between 2 words from list of 2 range columns?
I am unsure as I did not understand the rest of the post.

Could we have a bit more of simple sample data and expected results set out in two worksheets and in a form that we can copy from to test?
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top