extracting just numbers from a cell

Hey guys,

I have a problem. I have cells that look like this: "MBU_23.5" and alternatively some that look like this "34.0"

Is there any way I can just extract the numbers from these cells? ie, in the first example, I need just "23.5" and in the second "34.0".

Any simple way to do this? Does excel have a built in function for this?

Thanks!

Hello Dermot81

If the "_", character is a constant then use this.

=--MID(A1,FIND("_",A1)+1,LEN(A1))


Matt

Dermot81,

Dave Peterson posted this solution for the same problem a while back.

=VALUE(MID(A1,MIN(IF(ISNUMBER(--MID(A1,ROW($1:$10000),1)),ROW($1:$10000))),MAX(IF(ISNUMBER(--MID(A1,ROW($1:$10000),1)),ROW($1:$10000)))-MIN(IF(ISNUMBER(--MID(A1,ROW($1:$10000),1)),ROW($1:$10000)))+1))

This is an array formula and must be commited with Ctrl+Shift+Enter not just Enter.

HTH

Steve

Thanks all for the help.

Got it to work

Edit: Quick question, though; what does the "--" do?