extractind data after 2nd dash

Sanbiz94

New Member
Joined
Sep 26, 2011
Messages
28
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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

jeffreybrown

Well-known Member
Joined
Jul 28, 2004
Messages
5,149
Try...

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",2))+2,FIND("^^",SUBSTITUTE(A1,"-","^^",3))-FIND("^^",SUBSTITUTE(A1,"-","^^",2))-3)
 
Last edited:
Upvote 0

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,190
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
ADVERTISEMENT
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

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
5,283
Office Version
  1. 365
Platform
  1. Windows
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

taurean

Well-known Member
Joined
Jun 17, 2011
Messages
2,190
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
ADVERTISEMENT
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

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
38,154
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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,196,021
Messages
6,012,904
Members
441,739
Latest member
Jeezer

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
Top