Extracting numerical value

[masaatcs]

I have cells with alpha-numerical data and I'd like to extract last numerical value in the cell to another cell, this is a sample of the data: an-2-73mm. I would like to get that 73 extracted to another cell, how do I do this? The data is pretty similar in every cell, middle number varies from 1 to 60 and last number 50 - 150.

[Pete_UK]

Assuming that text is in A1, you could use this:

=SUBSTITUTE(MID(A1,FIND("-",A1)+1,3),"m","")*1

Hope this helps.

Pete

[masaatcs]

Hey Pete

Thanks for the formula, unfortunately it doesn't seem to work. If the data is "an-73mm" it work correctly, but if the data is "an-2-73mm" the result will be totally something else.

[Pete_UK]

You can make this slight change (in red):

=SUBSTITUTE(MID(A1,FIND("-",A1,4)+1,3),"m","")*1

Hope this helps.

Pete

[masaatcs]

Perfect, thank you very much!

[Pete_UK]

You're welcome - thanks for the rep.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Pete