Need to extract text between dashes (more than two)

[asolopreneur]

[TABLE="width: 500"]
<tbody>[TR]
[TD]TEXT STRING[/TD]
[TD]RESULT[/TD]
[/TR]
[TR]
[TD]WOW - WORD - LOVE - NF - CARD[/TD]
[TD]WORD[/TD]
[/TR]
[TR]
[TD]JOB - EXCEL - LOL - NL - COOL[/TD]
[TD]EXCEL[/TD]
[/TR]
[TR]
[TD]THUMB - OFFICE - ROFL - GR - AWESOME[/TD]
[TD]OFFICE[/TD]
[/TR]
</tbody>[/TABLE]

In above example you can see string I've and result I want.

Please help, I want to extract second word between 1st and 2nd dashes.

 

[jtakw]

Hi,

Would this work for you:

Book1
	A	B
1	TEXT STRING	RESULT
2	WOW - WORD - LOVE - NF - CARD	WORD
3	JOB - EXCEL - LOL - NL - COOL	EXCEL
4	THUMB - OFFICE - ROFL - GR - AWESOME	OFFICE
Sheet69

Cell Formulas
Range	Formula
B2	=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),100,100))

Formula copied down.

 

[asolopreneur]

Worked perfectly but I have some word with hypens for instance

TEXT STRING RESULT
WOW-1 - WORD - LOVE - NF - CARD WORD
JOB-2 - EXCEL - LOL - NL - COOL EXCEL
THUMB-3 - OFFICE - ROFL - GR - AWESOME OFFICE

Either we skip first hyper or we can extract data between second space and third space.

If possible can explain how the formula works in layman's word.

 

[jtakw]

Are there any other possible text strings?:

Book1
	A	B
7	TEXT STRING
8	WOW-1 - WORD - LOVE - NF - CARD	WORD
9	JOB-2 - EXCEL - LOL - NL - COOL	EXCEL
10	THUMB-3 - OFFICE - ROFL - GR - AWESOME	OFFICE
Sheet69

Cell Formulas
Range	Formula
B8	=TRIM(MID(SUBSTITUTE(A8," - ",REPT(" ",100)),100,100))

Formula copied down.

 

[asolopreneur]

Thanks a lot.

 

[jtakw]

You're welcome, so I assume it's working for you now.

The formula uses the SUBSTITUTE function to replace what's defined within the quotes, in this case, " - ", "space dash space", with 100 spaces, then the MID function starts at 100, takes the next 100 characters (spaces and all), and the TRIM function "cleans up" all the leading and/or trailing spaces, to result in the SECOND text string.