Extracting several words from a text string

[FKalinx]

Hi there,

I am looking at extracting several words from a text string.

In this example:

#interestedin-bus #interestedin-car #interestedin-train #interestedin-bus #interested-in-bike #interested-in-car

I need bus car train bus bike car returned ideally in multiple cells so I can analyse their frequency.

I have tried the =RIGHT(A2,LEN(A2)-SEARCH("-",A2)) but it only picks up the first instance. I then thought I could transpose the text into multiple cells and then run this formula but can't work out how to transpose using blank spaces.

Any help, much appreciated.

 

[shift-del]

Hi

=SUBSTITUTE(SUBSTITUTE(FILTERXML("<y><z>"&SUBSTITUTE(A1," ","</z><z>")&"</z></y>","//z"),"#interested-in-",""),"#interestedin-","")

 

[bebo021999]

Works for all versions of Excel.
With original text string in A2
Try in B2:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2&" ","-in",""),"-","@")," ","@"),"@",REPT(" ",100)),(COLUMNS($A:A)*2-1)*100,100))

drag accross

 

[FKalinx]

Hi

=SUBSTITUTE(SUBSTITUTE(FILTERXML("<y><z>"&SUBSTITUTE(A1," ","</z><z>")&"</z></y>","//z"),"#interested-in-",""),"#interestedin-","")

Thanks. Which cell do I put the original text? And what do Y and Z mean?

Would this also work in a Google Sheet?

 

[FKalinx]

Works for all versions of Excel.
With original text string in A2
Try in B2:

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2&" ","-in",""),"-","@")," ","@"),"@",REPT(" ",100)),(COLUMNS($A:A)*2-1)*100,100))

drag accross

Thanks - unfortunately it hasn't worked - see image. Any ideas? Thanks a million.

 

[bebo021999]

just copy/paste into cell B2, why did #REF! appear in formula bar?
In formula bar, try to remove #REF! with $A2
Does it work?

 

[shift-del]

Thanks. Which cell do I put the original text? And what do Y and Z mean?

Why not put it in A1?
y and z are just placeholders in the XML code. Name them anything you want.

Would this also work in a Google Sheet?

This is an Excel forum. I have no ideas about Google Sheets.

 

[FKalinx]

just copy/paste into cell B2, why did #REF! appear in formula bar?
In formula bar, try to remove #REF! with $A2
Does it work?

Thanks, yes it worked partially - see image. Cells F2 and G2 are not extracting correctly.

 

[bebo021999]

Thanks, yes it worked partially - see image. Cells F2 and G2 are not extracting correctly.

Could you paste the new text string here to help me to do some test.

 

[FKalinx]

Thanks Bebo021999. Here's the Excel file - cells highlighted in yellow show cells that have not been extracted correctly.

candidates_1.xlsx

Solution 1 #interestedin-cicd #interestedin-devops #interestedin-scripting #interestedin-devsecops #interestedin-security #interestedin-python,cicd,devops,scripting,devsecops,securi,#interestedin,,,, #interestedin-improvement #interestedin-terraform #interestedin-iac #interestedin-secretsmanage...

docs.google.com