Forum Discussion

Colin59's avatar
Colin59
Copper Contributor
Jun 24, 2022
Solved

Excel - problems converting string containing numbers and text to a number

Hi, I have a spreadsheet containing hundreds of rows with a column formatted as general which contain numbers and text, e.g. 1.45 TB, 276 GB etc. therefore preventing any math operations (sum, avg., etc) on the numbers, which I need to get.

I can use the SUBSTITUTE() to get rid of the text characters, leaving a number with a period as decimal point. If I convert the column to number it turns the "number" into a completely different number, e.g. 1.2 into 44593,00. Presumably this has to do with the sheet having been created on a German machine, whereas my locale is US. I have switched the language for excel to German (which uses a comma as decimal separator) but this makes no difference. I have tried replacing the . with a comma, converting to numbers, Multiply, and all the conversion tactics listed on https://support.microsoft.com/en-us/office/convert-numbers-stored-as-text-to-numbers-40105f2a-fe79-4477-a171-c5bad0f0a885 but none of them produce the results I need. My Version of Excel is 16.43 running on Mac OS X 10.15.7 (Catalina). Does anyone have a bright idea on how to do this?  

excel
Excel on Mac
Formulas and Functions
  • Colin59's avatar
    Colin59
    Copper Contributor
    Jun 24, 2022
    I have just tried using substitute a second time to replace the . with a , and then, in another column, =1*value(field with substituted separator) and that produces a number - finally. However there must be a more elegant way of doing this, without having to introduce three extra columns for each on I have to convert. The third column (the one containing the substituted decimal separator) is formatted as number. The first column (number + text) is General, the second is Text.
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 24, 2022

      Hi Colin59 

       

      If you always have a B (MB, GB, TB, PB) as below

      in B2:

      =VALUE(
    SUBSTITUTE(
        LEFT(SUBSTITUTE(A2," ",""), SEARCH("?B",SUBSTITUTE(A2," ","")) -1),
        ".", ","
    )
)

       

      If still not good please post a representative sample of your Text values

      • Colin59's avatar
        Colin59
        Copper Contributor
        Jun 28, 2022

        Lorenzo 

        My Excel refused to accept your formula, even if I reduced it to a single line. :o(

        I am stuck on Excel 16.43 (20110804) due to my OS not being upgradeable on this hardware. However, since the substitute commands work when they are applied individually to the column data I would have thought that formula should work. My three-extra-columns workaround does work though, it is just a trifle annoying.

Resources