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.

 

[FKalinx]

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


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

Thanks Shift-Del.

The formula is throwing up an error - see Solution tab here: candidates_1.xlsx

 

[Dan_W]

As a good rule of thumb - if you have a question re: Excel, go to an Excel forum (like here, for example). If you have a question re: Google Sheets, go to a Google Sheets forum.You do understand, don't you, that they're entirely different pieces of software?

The formula is not throwing up an error - in fact, the formula's very good. It is Google Sheets that is throwing up an error, and frankly I'm not surprised. It says - very clearly - "Error: Unknown function: 'FILTERXML'" That means that Excel-specific formulas are not understood on that completely different piece of software known fondly as Google Sheets.

Solution? Ask the question at a Google Sheets forum.

 

[bebo021999]

If you want to remove all "#interestedin-" out, then pick every alphabet textstrings:

=TRIM(MID(SUBSTITUTE(" "& TRIM(SUBSTITUTE(SUBSTITUTE($A2,"#interestedin-",""),", "," "))," ",REPT(" ",100)),COLUMN(A:A)*100,100))

 

[FKalinx]

As a good rule of thumb - if you have a question re: Excel, go to an Excel forum (like here, for example). If you have a question re: Google Sheets, go to a Google Sheets forum.You do understand, don't you, that they're entirely different pieces of software?

The formula is not throwing up an error - in fact, the formula's very good. It is Google Sheets that is throwing up an error, and frankly I'm not surprised. It says - very clearly - "Error: Unknown function: 'FILTERXML'" That means that Excel-specific formulas are not understood on that completely different piece of software known fondly as Google Sheets.

Solution? Ask the question at a Google Sheets forum.

View attachment 72368

I am aware that they are different bits of software, hence the original question. If I thought they were the same, I wouldn't have asked it...

I uploaded the Excel file to Google Drive so I could share it with the members here. If you don't download as an Excel file, Google will convert it into a Google Sheet. The error still stands in my Excel - see screenshot.

 

[FKalinx]

If you want to remove all "#interestedin-" out, then pick every alphabet textstrings:

=TRIM(MID(SUBSTITUTE(" "& TRIM(SUBSTITUTE(SUBSTITUTE($A2,"#interestedin-",""),", "," "))," ",REPT(" ",100)),COLUMN(A:A)*100,100))

You're a star, thanks so much Bebo021999. It works perfectly.

 

[bebo021999]

You're a star, thanks so much Bebo021999. It works perfectly.

13 posts to get the result!
Because you did tell us that data was stored in candidates "#interestedin-" what subjects, and need split the subjects out...
Cheer!