Thread: Extracting Text?

Hi,

I have the following text in cell D3:

HPT Stg 1 Blade

I thought this function could work to extract the "1" out of the cell & put into another cell:

=if(d3="*1*","1").

I am getting a false answer. What's wrong with this function? More importantly, how can I extract desired text out of it?

Thanks

Try this,

=LOOKUP(9E300,--MID(0&D3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},D3&"0123456789")),ROW(A$1:A$20)))

Try this out as well
=IF(ISERROR(FIND(1,D3)),"",1)

Hi Everyone,

Thanks for the feedback. I'll try these when I get back to work. I thought there was an Excel wildcard function that looked something like this: "*D3*". Is this correct or is it some other type of format?

Hi Everyone,

It appears that just extracting "1" is too broad of an extraction. I am looking to extract "Stage 1, 2, 3 or 4" out of the text found in column B & put a "1, 2, 3 or 4" result in column C.

Basically, if column B has "Stage" in it, I'm looking for the value, which should be 1, 2, 3 or 4 as the result & put result in column C.

I modified my original Excel file & took out a few columns.

See attachment.

Thanks

Will this do what you need?

=IF(LEFT(B3,5)="Stage",RIGHT(B3,1),"")

That should work up to Stage 9. If the number goes to two digits, the formula could be changed to find the space and take the digits beyond it.


Update - Looking at your spreadsheet again, I see the search for "Stage" needs to be more flexible. I'll give it more thought.

Hi Canuck Chuck,

Thanks for the info.

Is the following function case sensitive? My result is blank.

This function seems to work well:

=IF(ISERROR(FIND("STAGE",B3)),"",MID(B3,FIND("STAGE",B3)+6,1))

Oops,

I forgot to add in the function:
=IF(LEFT(B3,5)="Stage",RIGHT(B3,1),"")

Is this case sensitive?

For this instance, it does not have or should be. However, with some other applications that I may have in mind, it may have to be for other projects that I'll be working later.

Thanks

Hi,

I thought when I could get one of these functions working, I could modify to extract other data.

Now, I'm trying to extract "HPT" & "LPT out of the column B.

I thought I could use this:

=IF(ISERROR(FIND("*HPT*",B3)),"HPT",--IF(ISERROR(FIND("*LPT*",B3)),"LPT"))

It's finding "HPT", but not finding "LPT".

What's missing to combine these functions?

Thanks

Another one...

=LOOKUP(9E300,SEARCH({"?","HPT","LPT"},TEXT(B3,"@")),{"","HPT","LPT"})

SEARCH is NOT case sensitive

Hi Guys,

Thanks again for the info. These few functions work great!

That's all the questions I have at the moment....