Difficult extraction question

[flavius]

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?

 

[JoeMo]

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
	A	B
1	The cow_jumped over the-moon	jumped over the
2	The cow jumped over the-moon	jumped over the
Sheet5

 

[flavius]

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

 

[JoeMo]

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

You are welcome. Thanks for the feedback.

 

[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

 

[JoeMo]

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.

 

[farmerscott]

JoeMo,

most helpful.

thanks

FarmerScott