why text values are not converting to numbers

[chahine]

Hello guys

I have a file with values that are text, am trying to convert it using methods like

1. =value (text)

2. multiply by 1

3. using paste special multiply by 1

etc, but still all methods are not working its really strange never happened to me before

anyone knows what is the issue?

[#2]

@chahine 

Convert numbers stored as text to numbers

Numbers that are stored as text can cause unexpected results. Select the cells, and then click

to choose a convert option. Or, do the following if that button isn't available.

Use Paste Special and Multiply

If the steps above didn't work, you can use this method, which can be used if you're trying to convert more than one column of text.

1. Select a blank cell that doesn't have this problem, type the number 1 into it, and then press Enter.

2. Press CTRL + C to copy the cell.

3. Select the cells that have numbers stored as text.

4. On the Home tab, click Paste > Paste Special.

5. Click Multiply, and then click OK. Excel multiplies each cell by 1, and in doing so, converts the text to numbers.

6. Press CTRL + 1. Then select any format.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

[#3]

@chahine 

It depends on in which locale you are, more concrete what are your thousand and decimal separators.

You may select column O, Data->Text to Columns, and on third step select comma as thousand, dot as decimal separator.

Result is numbers

Alternatively you may change them in File->Options

but that affects all your Excel files.

[#4]

@Sergei Baklan I tried all methods that i know to convert to number, but its not working, did you ever faced such problem? i never had an issue like that

I will attach the file , thanks

[#5]

@chahine 

It is always better if you insert a file (without sensitive data) and also know the Excel version and operating system from the start. You have to make life even harder for the others who want to help, unless you don't want a solution.

Formula (english)=VALUE(A2)*1

Formel (German)==WERT(A2)*1

In the inserted file you will find an example on your values / sheet

If I may recommend that you read Mr. Cuong's post, the first message is in this forum.

There you can see the guidelines that will help you faster and more effectively.

Welcome to your Excel discussion space!

Cuong Pham (@cuong) in Excel  11-03-2021

Thank you for your patience and time.

Nikolino

I know I don't know anything (Socrates)

[#6]

there is no sensitive data in the file, & the file is from germany

My problem is that all the methods for converting are not working, this why am asking

[#7]

@chahine 

Text to Columns shall work in your case. If by formula, you may use

=NUMBERVALUE(A2,",",".")

Please check attached.

[#8]

strange why on my pc, its not working, its just giving an error #value
first time i face such issue that even text to column is not working, i tried also to change locale in the formatting of date to make it as original, but still its not working

[#9]

Hi again, it worked when i changed my advanced settings for . as thousand seperator & , as decimal

My question, is there an automatic way to detect locale, like if i frequently get files from other regions, i should manually modify it each time? and as i understand if i modify it, this setting will be active for my future files unless i change it, its not a setting for the current workbook/file

[#10 · Accepted solution]

@chahine 

Usually Excel do all transformations from from one locale to another automatically. Issues could be if people use texts instead of formatted, includes as dates or as currency, numbers. Or if you import text files. In which locale file was created that's only if check how such texts are formatted.

If you is able to have numbers by changing of advanced settings, when Text to Columns definitely shall work without such change. I have no idea why it didn't work in your case, if only on thirds stage of the wizard you have misprint in Advanced setting for decimal / thousand separator, or set them as for your locale, not as they are for source locale.

[#11]

@NikolinoDE 

Thank you for this suggestion.  It worked perfectly for me.  

[#12]

thank you for this simple and sensible work around! the x1 was brilliant