Forum Discussion
Excel number format for "less than" values
So I have an excel sheet that I am importing in to Power BI. In excel, I have some strange formatting of values like the following. What is going on with the value "< 0.12"? When I import it into Power BI, it reads it as 0.12 (which is what I want), but it reads the other values with the "<" sign as text.
Most of the other values in this sheet show up as text. For example, the "< 0.07" imports as text, not a number.
From the cell details, even in Excel it seems to showing as 0.12, but displaying as "< 0.12".
Can anyone explain what might be going on? Here is what these cells look like when imported in to Power BI. Ideally, whatever is happening I can apply to all the other cells so that the < 0.07's also show up as just 0.07 in Power BI too.
Hopefully this question makes some sense.
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.
7 Replies
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.
