How to extract "4/5.00" from cell with value "1.29(4/5.00 SAL)

[texhorn92]

What formula can I use to get this? Also, can someone explain why it works? I tried using something like this, but no success:
--MID(F2,FIND("(",F2)+1,10)

Thank you!

 

[Nine Zero]

Hey try this out....

=MID(F2,FIND("(",F2)+1,6)

You had 10 instead of 6 which gives the result "4/5.00 SAL"

so if we remove the last 4 which would be 10 - 4 we would get 6

 

[texhorn92]

What about if I wanted to extract the "5.00" from 1.29(4/5.00 SAL) or "6.00" from 1.69(3/6.00 SAL)

Thank you.

 

[texhorn92]

Sorry, I'd also like to know how to get the "4" from "1.29(4/5.00 SAL) using the MID function.

Thanks.

 

[Michael M]

=mid(f2,find("/",f2)+1,4)

 

[FlameRetired]

sorry, i'd also like to know how to get the "4" from "1.29(4/5.00 sal) using the mid function.

Thanks.

=mid(a1,find("(",a1)+1,mmult(find({"(","/"},a1),{-1;1})-1)

 

[FlameRetired]

What about if I wanted to extract the "5.00" from 1.29(4/5.00 SAL) or "6.00" from 1.69(3/6.00 SAL)

Thank you.

Another way.

=MID(A1,FIND("/",A1)+1,MMULT(FIND({"/"," "},A1),{-1;1})-1)

 

[texhorn92]

Hi FlameRetired,

I couldn't get =MID(A1,FIND("/",A1)+1,MMULT(FIND({"/"," "},A1),{-1;1})-1) to work.

 

[FlameRetired]

Hi FlameRetired,

I couldn't get =MID(A1,FIND("/",A1)+1,MMULT(FIND({"/"," "},A1),{-1;1})-1) to work.

It's still working at my end. What return(s) are you getting?

Check somethings while you are at it. Since this is the only formula referencing an empty space you might want to check for the presence of other non printable "spaces".

If you are not aware of it all characters have an ASCII numeric code. Characters that often cause problems and confusion are CHAR(160), CHAR(10) and CHAR(13). Actual space characters are CHAR(32).

One way to check would be =FIND(CHAR(160),A1). It should return an error if there is no CHAR(160). Ditto CHARs 10 and 13.

 

[texhorn92]

I think I know the problem. I didn't account for a space in my data. It should read 1.29 (4/5.00 SAL) not 1.29(4/5.00). Trying to isolate the value "5.00"

Thank you.