Forum Discussion
Solved
why text values are not converting to numbers
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?
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.
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.
Thank you for your patience and time.
Nikolino
I know I don't know anything (Socrates)
- 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 askingText to Columns shall work in your case. If by formula, you may use
=NUMBERVALUE(A2,",",".")Please check attached.
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.
SergeiBaklan 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
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.
Select a blank cell that doesn't have this problem, type the number 1 into it, and then press Enter.
Press CTRL + C to copy the cell.
Select the cells that have numbers stored as text.
On the Home tab, click Paste > Paste Special.
Click Multiply, and then click OK. Excel multiplies each cell by 1, and in doing so, converts the text to numbers.
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)
- thank you for this simple and sensible work around! the x1 was brilliant
