Forum Discussion
Excel number format for "less than" values
Thanks!
Some of these values have been entered as numbers, for example 0.12
The custom number format contains the fixed character < so the value is displayed as < 0.12
When you export the data to another application, you'll see 0.12 because that is the actual value of the cell.
Other values have been entered as displayed, complete with the character < in the value, for example < 0.12. Since < is part of the value, Excel treats it as text. The number format does not affect this, so whether the format is General or Text or 0.00 or < 0.00, Excel will display < 0.12
When you export to another application, you'll see < 0.12 - the real value of the cell.
Select C13, then press Ctrl+1 to activate the Format Cells dialog, and activate the Number tab.
What do you see there?
Here is what C13 looks like:
Also, here is what one of the other cells that imports as text looks like:
To me they both seem the same. What are you thinking??
I've tried to do a Format Paint from the one cell to the other, but it doesn't seem to make anything change.
To get rid of the < :
- Select all the cells.
- Press Ctrl+H to activate the Replace dialog.
- Enter < followed by a space in the 'Find what' box.
- Leave the 'Replace with' box empty.
- Click 'Replace All'.
- Set the number format to General or to Number.
OK, that makes sense. Thanks for explaining that.
So with the Find/Replace it seems to be replacing my "< 0.07" with "0.1". I guess it's a rounding issue, but I'll probably just update these few manually, as there aren't that many.
Thanks for the help!
Thanks!
Some of these values have been entered as numbers, for example 0.12
The custom number format contains the fixed character < so the value is displayed as < 0.12
When you export the data to another application, you'll see 0.12 because that is the actual value of the cell.
Other values have been entered as displayed, complete with the character < in the value, for example < 0.12. Since < is part of the value, Excel treats it as text. The number format does not affect this, so whether the format is General or Text or 0.00 or < 0.00, Excel will display < 0.12
When you export to another application, you'll see < 0.12 - the real value of the cell.
