# extractind data after 2nd dash

#### Sanbiz94

##### New Member
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
Try...

=MID(A1,FIND("^^",SUBSTITUTE(A1,"-","^^",2))+2,FIND("^^",SUBSTITUTE(A1,"-","^^",3))-FIND("^^",SUBSTITUTE(A1,"-","^^",2))-3)

Last edited:

#### T. Valko

##### Well-known Member
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))

#### Rick Rothstein

##### MrExcel MVP
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:

#### taurean

##### Well-known Member
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).

#### AhoyNC

##### Well-known Member
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.

#### taurean

##### Well-known Member
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.

#### Jonmo1

##### MrExcel MVP
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...

#### Rick Rothstein

##### MrExcel MVP
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.

#### Sanbiz94

##### New Member
Thanks Guys for helping me with this question @jeffreybrown Ur Formula work Great
ray:ray:

Replies
13
Views
2K
Replies
1
Views
440
Replies
5
Views
4K

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.

### Which adblocker are you using?

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

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