extractind data after 2nd dash

Sanbiz94

New Member
Joined
Sep 26, 2011
Messages
29
Hello Everyone,
Is there a formula or code that would only extract data after the 2nd to 3rd dash.
Physician - Orthopaedic Surgery - Hand Surgery - Augusta, GA
Physician - Orthopaedic Surgery - Sports Medicine - Birmingham, AL
Physician - Pulmonary Disease - Critical Care - Roswell, NM

Hand Surgery
Sports Medicine
Critical Care


Thanks for the help Guys...
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Try...

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",2))+2,FIND("^^",SUBSTITUTE(A1,"-","^^",3))-FIND("^^",SUBSTITUTE(A1,"-","^^",2))-3)
 
Last edited:
Upvote 0
Hello Everyone,
Is there a formula or code that would only extract data after the 2nd to 3rd dash.
Physician - Orthopaedic Surgery - Hand Surgery - Augusta, GA
Physician - Orthopaedic Surgery - Sports Medicine - Birmingham, AL
Physician - Pulmonary Disease - Critical Care - Roswell, NM

Hand Surgery
Sports Medicine
Critical Care


Thanks for the help Guys...
Assume data in the range A2:A4.

Enter this formula in B2 and copy down:

=TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),200,100))
 
Upvote 0
Here is a general formula for obtaining the Nth field number with a user specified delimiter...

=TRIM(MID(SUBSTITUTE(A1,[Delimiter],REPT(" ",LEN(A1))),LEN(A1)*([FieldNumber]-1),LEN(A1)))

Just replace the [Delimiter] with your delimiter and [FieldNumber] with the numerical position (1 being the field in front of the first delimiter), then perform the indicated math (subtracting 1 from the field number). So, for the second field in a text string with dashes as the delimiter, the above formula would become...

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1),LEN(A1)))

If you know your text string will never be more than 99 characters long, you could simplify the above to this...

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",99)),99,99))

It the text could be longer than 99 characters, then change all the 99's to 999.
 
Last edited:
Upvote 0
Re: extracting data after 2nd dash

Here's a FIND based variation.
=TRIM(MID(A2,FIND("-",A2,FIND("-",A2,1)+1)+1,FIND("-",A2,FIND("-",A2,FIND("-",A2,1)+1)+1)-(FIND("-",A2,FIND("-",A2,1)+1)+1)))

I guess you must be having some reason NOT to use Text to Columns (using "-" as delimited).
 
Upvote 0
Another option if you don't have to use a formula is the "Text to Column"
function in Excel. Use - as the delimiter.
Draw back is it will add 4 new columns to your sheet.
 
Upvote 0
Biff's & Rick's formula with REPT is really good. It gave me a new (limited for me) idea. Here's a variation:
=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),(2*LEN(A1)),LEN(A1)))
What I felt versatile about REPT variation that as soon as you changed the integer 1, 2, 3 (bold red part) it gave respctive string after the nth "-". Thank you guys.
 
Upvote 0
Another option if you don't have to use a formula is the "Text to Column"
function in Excel. Use - as the delimiter.
Draw back is it will add 4 new columns to your sheet.

Text to columns is a good solution...

As far as that drawback, you can choose to NOT include the extra columns on the 3rd page of the wizard...
 
Upvote 0
Biff's & Rick's formula with REPT is really good. It gave me a new (limited for me) idea. Here's a variation:
=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),(2*LEN(A1)),LEN(A1)))
What I felt versatile about REPT variation that as soon as you changed the integer 1, 2, 3 (bold red part) it gave respctive string after the nth "-". Thank you guys.
Just to point out, the above formula is what my general formula becomes after the substitutions I indicated, except you removed the -1 after the FieldNumber (which is why the red number gives you the field after the nth dash as opposed to the actual field number as I indicated in my posting). Personally, I think keeping my original general formula (with the minus one) is more intuitive... you count fields, not dashes... seems more logical to me. But, hey, if that formula works for you, and you don't mind thinking 0 is a position "after" a dash when you need to retrieve the first field (here I am thinking of a situation where the FieldNumber is kept in a cell for dynamically retrieving whatever field number you want at the time), then great.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
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