You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Converting text from a text string to number

Advertisement

QUESTION: Hi Tom,

I have been trying to pick part a text string which seems to be going ok however my extracted data I cannot convert to a number. I have tried to format the cell to make it a number to no avail.

Sample Data

HSS 25/64x3/8x1x2-11/16

HSS 5/8x5/8x2-1/2x4-5/8

HSS 5/8x5/8x2-1/2x4-5/8

Code to pick apart string, work in progress as I have to get each fraction out of the string.

=TRIM(LEFT(SUBSTITUTE(MID(A21,FIND("|",SUBSTITUTE(A21,"x","|",2))+1,LEN(A21)),"x",REPT(" ",LEN(A21))),LEN(A21)))

this results in the answer below

2-1/2

This result I then replace the hyphen with a space (I would enter it this way if entering 2 1/2 in cell so I would assume this would be correct)

=REPLACE(E21,2,1," ")

With below result

2 1/2

It looks correct but I need the result to be 2.500. Any formatting I try with it is 2 1/2

I am using Excel 2010.

Thanks if you can help me out. You definitely have in the past.

Denny

ANSWER: Denny,

if I have 2-1/2 in E21 then

=REPLACE(E21,2,1," ")*1

formatted as general or number with more than zero decimal places will display as

2.5

or

2.50

so multiplying by 1 forces it to be a number (or would return an error if the string could not be converted to a number).

--

regards,

Tom Ogilvy

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Thanks Tom!!!!

It worked with single digit, double digit did not

Single

HSS 5/8x5/8x2-11/16x13-5/8 -------> 2-11/16

Results -------> 2.6875

=REPLACE(E21,2,1," ")*1

Double

HSS 5/8x5/8x12-11/16x13-5/8 -----> 12-11/16

Results -----------> 42380

The additional number throws it off.

My formula extracting some of the data from the original string needs some attention also due to additional numbers as in if 13 is used instead of 3 and if you would prefer I will follow up with another question with regards to that issue. The only thing consistent with the data that was done ages ago was it was inconsistent.

Thanks again

Denny

ANSWER: For me

=REPLACE("2-11/16",2,1," ")*1

Produces: 2.6875

you seem to be showing a date serial number. 42380, if formatted as date comes out to be 1/11/2018 (January, 11, 2016)

so it appears to be treating it as a date although that seems to be a stretch.

perhaps use

=REPLACE(E21,2,1,"+")*1

so it becomes "2+11/16"*1

That should remove any propensity to treat it as a date.

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Okay, I see the issue, it is the start_num value

=REPLACE("2-11/16",2,1," ")*1

Produces: 2.6875

^^^^^^^this is correct

Note change below to 12-11/16 from 2-11/16

=REPLACE("12-11/16",2,1," ")*1

Produces: 42380

I changed the start_num to a value of 3 and it works

=REPLACE("12-11/16",3,1," ")*1

Produces: 12.6875

Is there a way to count number of characters for start_num?

There are many different fractions within the data, some with 3 digits (3/8), 5 digits (11/64), 5 digits (4-5/8), 8 digits (12-13/16) and so on that need converted to decimal all within the same string.

Thanks again sir.

Denny

Denny,

for cell E21, this worked for me with all the examples you gave.

=IF(ISNUMBER(SEARCH("-",E21)),LEFT(E21,SEARCH("-",E21)-1)*1,0)+(LEFT(IF(ISNUMBER(SEARCH("-",E21)),TRIM(SUBSTITUTE(E21,LEFT(E21,SEARCH("-",E21)),"")),TRIM(E21)),SEARCH("/",IF(ISNUMBER(SEARCH("-",E21)),TRIM(SUBSTITUTE(E21,LEFT(E21,SEARCH("-",E21)),"")),TRIM(E21)))-1)*1)/(1*MID(IF(ISNUMBER(SEARCH("-",E21)),TRIM(SUBSTITUTE(E21,LEFT(E21,SEARCH("-",E21)),"")),TRIM(E21)),SEARCH("/",IF(ISNUMBER(SEARCH("-",E21)),TRIM(SUBSTITUTE(E21,LEFT(E21,SEARCH("-",E21)),"")),TRIM(E21)))+1,255))

I know it appears verbose, but to avoid the automatic conversion to a date value I have broken out the numerator and denominator and forced division. If I think of a simpler approach, I will let you know.

--

Regards,

Tom Ogilvy

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | That works great Tom!!! I wish I understood the formula to pick apart the rest of the text string. Another question will probably be heading your way.. Top notch sir!! Denny |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.