Extracting numbers from a text string to use in a calculation

[bigalrm1]

I am trying to extract numbers, some of which are fractions, from a text string to use in calculating the weight of the material in a list.

Example from cell D14: Bar 4x1/4

I was able to extract the "4" with =VALUE(MID(D14,SEARCH(" ",D14,1)+1,(SEARCH("X",D14,1))-(SEARCH(" ",D14,1)+1))) but I used =VALUE(CLEAN(RIGHT(D14,(LEN(D14)-(SEARCH("X",D14,1)))))) without the "VALUE" function to get the "1/4" which was great but when I add the "VALUE" function I get "42008" as the value not ".25" as expected. The "CLEAN" function was added only in an effort to get rid of any extra characters that may be causing the value to come out wrong. I had also tried "TRIM" for the same purpose.

The puzzling part is that I was able to extract a fraction when it was with a whole number and use it in a calculation.

Example from cell D17: BAR 1 1/2x3/8

I used =VALUE(MID(D17,SEARCH(" ",D17,1)+1,(SEARCH("X",D17,1))-(SEARCH(" ",D17,1)+1))) to turn the "1 1/2" into a workable number. Worked great for "1 1/2"! I had also tried the "MID" function to extract the "1/4" and was successful but when I added the "VALUE" function I got the same "42008" value. What am I doing wrong or what function do I need to add to make "1/4" a workable number?

 

[C Moore]

That's funny, it really wants to return the date. I was able to get it to evaluate in the name manger. This won't exactly answer your question, but should get you in the right direction. I selected the cell I wanted the output in, opened name manager, new name fraction_pull, refers to =EVALUATE(RIGHT('Sheet1'!A2,3)). Not using absolute references ($) in the name manager can be tricky, so pay attention to what you have clicked when you enter it. you can convert the right function to mid with find to make it dynamic.

 

[bigalrm1]

That's funny, it really wants to return the date. I was able to get it to evaluate in the name manger. This won't exactly answer your question, but should get you in the right direction. I selected the cell I wanted the output in, opened name manager, new name fraction_pull, refers to =EVALUATE(RIGHT('Sheet1'!A2,3)). Not using absolute references ($) in the name manager can be tricky, so pay attention to what you have clicked when you enter it. you can convert the right function to mid with find to make it dynamic.

I am a seriously novice EXCELer, just so you know, but I looked for the "EVALUATE" function in my list of functions and it is not there. Also did a search in my EXCEL 2013 help window with no luck. Is that an old function or something or are you talking about the function you can click on next to where it shows the cell contents just under the tool bar and above the general array of cells?

 

[bigalrm1]

Thanks C Moore for your help. I was able to use the Evaluate Formula on the Formulas tab to discover that the problem comes down to =VALUE("1/4"). The quotes are in the way. I entered =VALUE(1/4) into a cell and got what I needed, 0.25. Now how do I get rid of the quotes in my formula? I thought VALUE was supposed to convert text to a number. Well, it did but related to what?

 

[C Moore]

EVALUATE does appear to be some kind of hidden function. 1/4 just really wants to be the date, can you not make the named formula work?

 

[Rick Rothstein]

As long as the numbers are always separated by an "x" and they always are located at the end of the text, these two formulas will return the numbers as floating point values whether they are integers, fractions or mixed numbers...

Left value
--------------------------
=IFERROR(0+TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(LEFT(A1,SEARCH("x",A1)-1))," ",REPT(" ",100)),200),200)),0+TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(LEFT(A1,SEARCH("x",A1)-1))," ",REPT(" ",100)),100),100)))


Right value
--------------------------
=0+TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"x","x 0 ")," ",REPT(" ",100)),200),200))

Note: As written, the first formula will work even if the number before the "x" is a floating point value, but the second one won't as it is set up to work with integers, fractions or mixed numbers only. If your data is such that the number after the "x" could also be a floating point number, then use this formula to get the number after the "x" instead...

=IFERROR(0+TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"x","x 0 ")," ",REPT(" ",100)),200),200)),0+TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"x","x ")," ",REPT(" ",100)),200),200)))

 

[bigalrm1]

C Moore, all I got with your formula was "INVALID FUNCTION." Must not be available in Excel 2013. Rick, I haven't tried yours yet but thanks for helping.

 

[Rick Rothstein]

Rick, I haven't tried yours yet but thanks for helping.

Do you plan to try them (we are only talking about a simple copy/paste to get them into your worksheet)?

 

[bigalrm1]

Rick, I did just that and adjust the cell references and it worked great. Thanks! Now I need to study your formulas so I will understand how to use them in the future.