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., ...
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.

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Jun 28, 2022

      Hi Colin59 

       

      I'm not a Mac user and have no idea at all what 16.43 means in term of Excel version (2010, 2013...2021 or 365). Just in case the VALUE function would not be avail. on 16.43 could you try the following?

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

       

      • Colin59's avatar
        Colin59
        Copper Contributor
        Jun 30, 2022
        I have tried this out and excel refuses to accept it as a valid formula. Search returns the position of the first occurrence of the search string. It does not accept using substitute in a nested form like this, it seems. I have since upgraded the version to the latest offering from Microsoft Office Suite (v 16.62) which probably equates roughly to Office 2019. However - not much info from MS about how Mac versions match Windows versions.

Resources