# Difficult extraction question

#### flavius

##### New Member
I need to extract a string from within a string. I want all the string between the first underscore and the first hyphen. The catch is, there may not be a first underscore, in which case I want the string up to the first hyphen.

What is the simplest most efficient function to use for this?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
I need to extract a string from within a string. I want all the string between the first underscore and the first hyphen. The catch is, there may not be a first underscore, in which case I want the string up to the first hyphen.

What is the simplest most efficient function to use for this?
Does this do what you want? Copy cell B1 formula down.
Excel Workbook
AB
1The cow_jumped over the-moonjumped over the
2The cow jumped over the-moonjumped over the
Sheet5

Wow! Thanks much. That does exactly what I need. Thanks for the speedy response!

Wow! Thanks much. That does exactly what I need. Thanks for the speedy response!
You are welcome. Thanks for the feedback.

hi JoeMo,

just wondering if you could explain the formula for me......

I get the individual parts of the formula (except the reason for the 'isnumber'), but quickly get lost seeing it as a whole.

thx

Farmerscott

hi JoeMo,

just wondering if you could explain the formula for me......

I get the individual parts of the formula (except the reason for the 'isnumber'), but quickly get lost seeing it as a whole.

thx

Farmerscott
SEARCH returns the position of the character or characters searched for as a number. ISNUMBER returns true if its argument is a number, false if not. So the first part of the formula tests to see if the underscore character can be found in the text. If yes then the MID function isolates the portion of the text between the underscore and the hyphen. If no then the LEFT function returns everything from the start of the text up to the first hyphen.

JoeMo,

thanks

FarmerScott

Replies
4
Views
185
Replies
3
Views
219
Replies
16
Views
363
Replies
7
Views
302
Replies
2
Views
598

1,207,095
Messages
6,076,552
Members
446,213
Latest member
bettigb

### 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