Need to extract text between dashes (more than two)

asolopreneur

New Member
Joined
Nov 15, 2017
Messages
40
Platform
  1. Windows
TEXT STRINGRESULT
WOW - WORD - LOVE - NF - CARDWORD
JOB - EXCEL - LOL - NL - COOLEXCEL
THUMB - OFFICE - ROFL - GR - AWESOMEOFFICE

<tbody>
</tbody>

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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hi,

Would this work for you:


Book1
AB
1TEXT STRINGRESULT
2WOW - WORD - LOVE - NF - CARDWORD
3JOB - EXCEL - LOL - NL - COOLEXCEL
4THUMB - OFFICE - ROFL - GR - AWESOMEOFFICE
Sheet69
Cell Formulas
RangeFormula
B2=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),100,100))


Formula copied down.
 
Upvote 0
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.
 
Upvote 0
Are there any other possible text strings?:


Book1
AB
7TEXT STRING
8WOW-1 - WORD - LOVE - NF - CARDWORD
9JOB-2 - EXCEL - LOL - NL - COOLEXCEL
10THUMB-3 - OFFICE - ROFL - GR - AWESOMEOFFICE
Sheet69
Cell Formulas
RangeFormula
B8=TRIM(MID(SUBSTITUTE(A8," - ",REPT(" ",100)),100,100))


Formula copied down.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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