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

[satish78]

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

Hightail Spaces

Hightail Spaces lets you send unlimited files, of any size, to anyone. Collaborate with your team and evolve your work.

spaces.hightail.com

 

[satish78]

Peter, Can you help me with Sandy's power query converting into VBA

 

[sandy666]

result from your example
text before the last comma and last keyword found in the list (sheet2)

but unfortunately Power Query ? ?

 

[Peter_SSs]

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.

 

[sandy666]

@Peter_SSs
I can confirm your code works well and there is nothing to change
maybe op want column with the last keywords?

 

[Peter_SSs]

@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.

 

[satish78]

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

 

[pjmorris]

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

 

[Peter_SSs]

Its working now.

Thanks for helping me in putting efforts to solve this query.

Good news. you're welcome.

If you simply want to extract all the words before the final comma

Hi Peter, the actual requirement was a bit more complex than that.

 

[satish78]

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

 

[Peter_SSs]

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?