problem with numeric format when downloading from external source

alisam58

New member
Joined
Jul 27, 2015
Messages
3
Reaction score
0
Points
0
hello all
when downloading data to an excel sheet (office 2010) from am external application (SAP) I have some numeric values that I need to sum.
the problem is that excel does not register the numeric formatting, on many levels.
the number appears as 0.000,000 (dot is thousand separator and comma is decimal separator)
even when selecting the cells and changing the format from "general" to numeric, Excel does not register the change.
I must change the number completely to 0000.00 AND to format the cell to "numeric" for excel to register that it is a numeric value.
How can I default the cells to be numeric and register the value as a numeric value?
 
Go to FILE - OPTIONS - ADVANCED. Uncheck the box for USE SYSTEM SEPARATORS.
 
thanks it helped in the sense that I get the error/message that the numbers are store as text: is there a way then to "correct" all the columns in one shot? if I select them and format to numbers is apparently not sufficient and if I select a cell that does not contain the error, the icon helping me to correct disappears
 
I think it shouldn't be hard to convert the text to an actual number. In a new column, use the VALUE function to convert the text number to a number. Then COPY - PASTE SPECIAL - VALUE over top of the original column.

PLEASE make a copy of your spreadsheet first and practice with the copy until you understand the process and can make it work.

Hope this helps!
cbug
 
is there a way then to "correct" all the columns in one shot?
Try you use Find/Replace (Ctrl+H) (time about 30 seconds)

Select only cells with problem

Find field: dot => Replace field: # => Replace All button
Find field: comma => Replace field: dot => Replace All button
Find field: # => Replace field: comma => Replace All button

Select all cells => Format cells => General
 
Candybc i tried but it does not work: what I get is the little triangle on the left top of the cell reporting an error and the error is that the number is stored as text: I can change that but it is over 8000 lines and I am trying to do that by selecting the columns but it does not work.
Navic: that is what I did in fact but as this is a report that must be regularly downloaded to excel and not a one time thing, I was trying to have this done in an easier way. Is there a Macro I can create for that?
 
Navic: that is what I did in fact but as this is a report that must be regularly downloaded to excel and not a one time thing, I was trying to have this done in an easier way. Is there a Macro I can create for that?
Can you attach small sample worksheet (imagination data)
 
Back
Top