extract a value from a cell

[vito01]

Hi,

I have got a cell with content of texts and values and wish to extract a value from it

I have attached a sample for clarity

Thanks

Vito

[MarvinP]

Hi vito,

See if this works for you.

Extract Value from String with -SERV.xlsx

If not then give more examples of your strings.

Edit: I had to see how "pretty" my formula was. It looks like this:

=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(B3,FIND("-",B3)-15,15))," ",REPT(" ",50)),50)) 

[vito01]

hey thanks, it works great!!!!

[CAntosh]

I don't know if it's the most efficient formula [EDIT: Marvin's is prettier], but I think this will work in D3:

=MID(B3,FIND("@",SUBSTITUTE(LEFT(B3,SEARCH(",",B3))," ","@",LEN(LEFT(B3,SEARCH(",",B3)))-LEN(SUBSTITUTE(LEFT(B3,SEARCH(",",B3))," ",""))))+1,SEARCH(",",B3)-FIND("@",SUBSTITUTE(LEFT(B3,SEARCH(",",B3))," ","@",LEN(LEFT(B3,SEARCH(",",B3)))-LEN(SUBSTITUTE(LEFT(B3,SEARCH(",",B3))," ",""))))+2)

Fill down as needed. It is predicated upon finding the first comma and returning everything from the preceding space through two characters after the first comma. As long as column B fits that mold, it should work.

Note that Excel will recognize the result in D3 as a text string, but you can add a "+0" should you need it be recognized as a number.

[vito01]

hey, and if I need to select the second value after the text "-SERV"?

[CAABYYC]

D3=TRIM(MID(SUBSTITUTE($B3," ",REPT(" ",50)),FIND("^",SUBSTITUTE(SUBSTITUTE($B3," ",REPT(" ",50)),",","^",COLUMN(A1)))-20,50))

copy to right to extract the 2nd,3rd,4th....*,* value (if has)