Sherry Chupka reports:
I loaded a CSV file into Excel 2007 and noticed some of the cell outlines were missing.
I copied the value 3045.45 to enter it in a bug report. After that the value was no longer aligned and some of the cell outlines to the right are missing.
I loaded the file in another editor and found out there are spaces at the end of each record.
Seems that when I copied the numeric value Excel automatically changed the format of that cell from text to numeric and removed the trailing spaces. There are 3202 rows in this file. Does that mean I have to copy the last value on all the rows? Then I formatted the column using Format Cells. Why doesn’t Format Cells remove the trailing spaces?
Maybe it should ask if you want to remove spaces. The trailing spaces were only removed from the cells where I copied the numeric portion of the value. I guess Excel doesn’t like spaces after a numeric value.
I loaded the file in another editor and found out there are spaces at the end of each record.
Seems that when I copied the numeric value Excel automatically changed the format of that cell from text to numeric and removed the trailing spaces. There are 3202 rows in this file. Does that mean I have to copy the last value on all the rows? Then I formatted the column using Format Cells. Why doesn’t Format Cells remove the trailing spaces?
Maybe it should ask if you want to remove spaces. The trailing spaces were only removed from the cells where I copied the numeric portion of the value. I guess Excel doesn’t like spaces after a numeric value.
Ben Simo adds:
Numbers entered or imported into Excel are often treated as text. Changing the display format generally doesn't make any difference in whether data is treated as text or numbers.
This often creates problems. Microsoft is aware of this and addresses it in the help. The Excel 2003 help states "Occasionally, numbers may be formatted and stored in cells as text, which later can cause problems with calculations or produce confusing sort orders."
So, what can we do about this without manually editing thousands of data cells? If we perform math on the cells, Excel will change the values to numbers. The easiest way I've found to handle this is to either add zero to all the cells, or multiply all the values by one. This can be done by using the Paste Special feature.
By adding 0:
- Enter a value of 0 into a blank cell.
- Select the cell with the value 0, and copy the cell to the clipboard
- Select the numeric cells that Excel is treating as text
- Select Paste Special
- Select Add
- Click OK to add 0 to the selected cells.
- Enter a value of 1 into a blank cell.
- Select the cell with the value 1, and copy the cell to the clipboard
- Select the numeric cells that Excel is treating as text
- Select Paste Special
- Select Multiply
- Click OK to add 0 to the selected cells.
Yes, there is a problem here. There is a fix but is isn't very obvious.
1 Comment:
February 28, 2013 at 1:47 PM-
Comment ID:
4267418130446756375
-
-
Written by: Anonymous
I was trying to consistently extract the latter part of "VBN132.45". Since the number at the end could change with length, I made sure I used the Mid command "=mid(a1,4,8)" with a1 the target cell to read from.
The output was 132.45, but it was text and not a number. So I could not use it to add against other cells.
To make matters worse, using the mid in an empty resulted in an ugly error. So what I did was:
=IF(A1>0,SUM(MID(A1,4,8)+MID(A1,4,8)-MID(A1,4,8))," "). So, it checked the cell to ensure it was not empty, and if so, would take the needed numbers out, added then subtracted against itself, and this ensured they were converted in Excel's eyes to an actual number.
Not sure if anyone has better ideas, but this did work.
Post a Comment