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

[Colin59]

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-4... 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?  

[#2]

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.

[#3]

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

[#4]

@L z. 

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.

[#5]

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

[#6]

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.

[#7]

Hi @Colin59 

Could you please share (i.e. with OneDrive, Google Drive...) a workbook with a representative sample of your Text values and next to them the Numeric values you expect, something like:

Thanks

[#8]

https://docs.google.com/spreadsheets/d/12fXmWMhAtQTdB0dGBH2qYLiX4DtwSKcP/edit?usp=sharing&ouid=11426...

[#9 · Accepted solution]

@Colin59 

Download the attached file and check how it goes on your side please

[#10]

That works fine. Many thanks for the effort.

[#11]

Glad I could help @Colin59 

I suspect there was no formula issue since the beginning

As you've observed this site is very EN-US driven. So, when someone post a formula the delimiter(s) inside that formula is the comma (,)

No idea what your usual formula delimiter is but let suppose it's the semi-colon (;). In such case when you're proposed something like:

=SUBSTITUTE(A2, " ", "")

you should replace the 2 commas with 2 semi-columns:

=SUBSTITUTE(A2; " "; "")

before trying to enter/validate the formula