Convert Less-Than Text Values to Numbers in Excel and Power BI
Question details
Convert values containing a less-than sign into usable numbers while preserving the intended less-than display where needed.
- Product
- Microsoft Excel and Microsoft Power BI
- Device & OS
- not provided
- Scenario
- A worksheet or imported data source contains values displayed or entered as less-than thresholds, such as < 0.12 and < 0.07.
- Observed behavior
- A numeric value formatted to display a less-than sign may import as a number, while a value typed with the less-than sign is treated as text.
Check whether the less-than sign is part of the cell's custom number format or is actually typed into the cell value. This determines whether the value is already numeric or must be transformed before import.
Identify custom-formatted numbers and text entries in Excel
Use this method when you need to determine why similar-looking cells behave differently during import or calculation.
A numeric cell can display a fixed less-than sign through a custom number format, such as "< "0.00, while its underlying value remains numeric. By contrast, an entry typed as < 0.07 is text because it includes a non-numeric character.
In Excel, click a cell that displays a less-than value, such as the cell showing < 0.12.
Press Ctrl+1, then select the Number tab and choose Custom to inspect the format code.
Look in the Formula Bar. If the Formula Bar shows only 0.12 while the cell shows < 0.12, the cell is numeric and the sign comes from formatting.
Use a formula such as =ISNUMBER(A2) in an empty cell. FALSE indicates that an entry such as < 0.07 is stored as text and needs conversion before import.
Transform typed less-than values in Power Query before loading
Use this method when source values such as < 0.07 are text and must become numeric for Power BI calculations, sorting, or filtering.
Convert less-than text to numbers with an Excel formula
Use this method when you want to create a clean numeric helper column in Excel before exporting or importing the data.
Clean less-than values and format numeric results in WPS Spreadsheet
WPS Spreadsheet can separate the less-than symbol from typed values, convert the remaining text into numbers with formulas, and apply a custom number format to show threshold-style values consistently.
- 1. Open the workbook in WPS Spreadsheet: Open the Excel workbook, then locate the column containing values with less-than signs.
- 2. Create a numeric helper column: Insert a column beside the source data and select the first cell in the new column.
- 3. Enter the conversion formula: Type =VALUE(SUBSTITUTE(A2,"<","")) and press Enter, replacing A2 with the first source cell.
- 4. Fill the formula through the data: Drag the fill handle down to convert the remaining less-than text values.
- 5. Set an optional custom display format: Select the converted cells, open Format Cells, choose Custom, and apply "< "0.00 when you want numbers to display with a fixed less-than sign.
Frequently Asked Questions
Why does < 0.12 import as a number but < 0.07 import as text?
The first value may be stored as 0.12 with a custom number format that displays a fixed less-than sign. The second value was likely typed with the < character, making the cell text.
How can I tell whether a less-than value is numeric in Excel?
Select the cell and inspect the Formula Bar, or use =ISNUMBER(A2). A numeric underlying value returns TRUE even if a custom format displays a less-than sign.
What is the best way to convert < 0.07 in Power BI?
Select Home > Transform data, duplicate the column, use Transform > Replace Values to remove <, then set the duplicate column to Transform > Data Type > Decimal Number.
Can I keep the less-than sign after converting values to numbers?
Yes. Keep the original text column for reporting, or apply a custom Excel format such as "< "0.00 to a numeric column. Note that this format displays the sign for every formatted value.
Will removing the less-than sign change the meaning of the measurement?
It can. The converted number represents the stated threshold, not an exact measured result. Keep an indicator or original text column when the distinction matters for analysis.
