OmniDocConvert Excel numbers to text: add single quotes and text format conversion
Explain the common reasons why Excel numbers are turned into text, explain the role of leading single quotes, how to add single quotes in formulas, and give several reliable methods to convert text numbers back to numerical values.
Key points of this article
- If there is a small green triangle in the upper left corner of the cell and the numbers are aligned to the left, it usually means that it is text, and sums and counts will be missed.
- The leading single quote is the text prefix when input and will not be displayed in the cell. It is suitable to retain numbers starting with 0 such as 001 and ID number.
- Changing to "regular" format only affects the content entered later, and existing text numbers need to be reprocessed.
- Text numbers can be converted back to numeric values using the error-checked "Convert to Number", column-by-column, or multiply-by-1 formulas. Numbers longer than 15 digits should remain text.
When you enter 001, ID number or a long string of numbers in Excel, after pressing Enter, it turns into text with a small green triangle in the upper left corner, and the summation result is incorrect. Many people subconsciously change the cell format, but the numbers remain unchanged after changing to "regular". This situation is especially common when sorting web page data and exporting tables from the system.
This article is written for office workers, students, and financial personnel who need to organize forms. After reading, you will be able to determine why numbers are turned into text, understand how leading single quotes work, and learn several methods to reliably convert text numbers back to numeric values.
If the number becomes text, how to change it back to a numerical value? One sentence conclusion
First look at the purpose of the data and then decide the direction: if you want to participate in calculations, convert it back to a numerical value; if you want to keep the starting 0 of the number, keep it as text and add leading single quotes when entering. Error checking is faster when there are only a few cells.
| your situation | Recommended practices | reason |
|---|---|---|
| Numbering data, such as 001, ID number | Keep text, enter with leading single quotes | The text can completely retain the leading 0 and digits |
| The cell is text and needs to participate in the sum or calculation | Complete with columns, or use a formula that is multiplied by 1 and then pasted as a numerical value | Text is not treated as a number by the calculation area |
| only a few cells | Click the yellow exclamation mark that appears next to it and select the item that converts to a number in the menu | Process one by one with less operations |
| You need to add single quotes before the content in batches (such as for export) | Use the ="'"&A1 formula to generate it, and then copy it as a numerical value | Formula results will contain single quote characters |
| Number exceeds 15 digits | Keep text, don't convert back to numeric value | Excel only retains 15 significant digits, and the following digits will become 0 |
First determine what kind of problem it is
The same "number is wrong", the reason may be different. First check the table below to confirm, and then choose the method.
| symptoms | Common causes | How to confirm |
|---|---|---|
| There is a small green triangle in the upper left corner of the cell, and the numbers are aligned to the left | Cells are text, commonly seen in web page copy or system export | Select the cell to see the edit bar, or enter =ISTEXT(A1) in the blank space, and return TRUE as text |
| Sum and count results are less than expected | Some numbers are text, and functions such as SUM are not included. | Use an ISTEXT check on the entire column to find the rows that return TRUE |
| No change after changing to "regular" format | The format only affects the content entered later, and existing content will not be automatically converted. | Double-click the cell and press Enter. If it is still aligned to the left, it means it is still text. |
| After entering 001, it will be displayed as 1 | The cell is in numerical format, and the leading 0 has been removed. | Check if cell format is numeric or general |
| The last few digits of the long number become 0 | After more than 15 significant digits, Excel will set the following digits to 0 | Count the digits. If it exceeds 15 digits, save it as text. |
Check and repair item by item
- Locate the text cell first. Enter =ISTEXT(A1) in the blank column and fill it down. What returns TRUE is the text number.
- A small number of cells, converted with error checking. Select these cells, click the yellow exclamation mark that appears next to it, and select the item that converts to numbers in the menu.
- To convert the entire column, use split columns. Select the entire column, go to the data ribbon at the top, start the column sorting function, and go to the last step to end it directly without modifying the default options. Text numbers are usually converted to numeric values.
- Use formula conversion, suitable for situations where the data is more complex. Enter =A1*1 or =VALUE(A1) in the blank column, fill it down, then copy the result, put it back in place using Paste Special with only the value, and then delete the auxiliary column.
- Clean up excess spaces. If the formula returns #VALUE!, it is probably because there are spaces or invisible characters before and after the number. Use =TRIM(A1)*1 instead, remove the spaces first and then calculate.
- Check the number before conversion. If the column contains an employment number, postal code or ID number, do not convert it to a numerical value, otherwise the leading 0 and the trailing digits may be lost.
- Remove the single quotes that have been entered. Single quotes are input prefixes and are not displayed in cells. To remove it, just re-enter the content without single quotes, or use column processing.
How do leading single quotes work?
Enter '001' in the cell, and Excel will save the content as the text "001". Only 001 will be displayed in the cell, but the leading single quote can be seen in the edit bar. This single quote only tells Excel to "process as text" and does not belong to the cell content.
Therefore, when entering the number, it is safer to add leading single quotes than to change the format later. It should be noted that if the cell content is copied to other systems, single quotes may be retained as characters.
How to add single quotes in formulas
If you need to display single quotes in formula results, such as batch generating numbers for export, you can use text connectors:
="'"&A1
A single quotation mark should be placed between a pair of double quotation marks because it is ordinary text in the formula. The result is a text with single quotes, which can be copied to a numerical value when needed to avoid problems caused by formula references.
Why changing to "regular" format doesn't work
Cell formatting determines how Excel interprets subsequent input. For existing text, even if the format is changed to "normal", the content is still text. This is the reason why many people think "formatting is invalid".
The correct approach is: after changing the format, re-enter the original data (double-click and press Enter), or use methods such as splitting into columns or multiplying by 1 to convert.
How to avoid it happening again next time
- Before entering the number, format the column as text and then enter the content.
- Long numbers (ID number, bank card number) are always saved as text and noted in the column title.
- For data exported from a web page or system, paste it into a blank column first and check it with ISTEXT before using it.
- When splicing results using formulas, if further calculations are needed, remember to copy them as numerical values to avoid formula reference errors.
- If the data comes from a PDF table, you can use the PDF editor of the OmniDoc desktop client to export it to Excel, and then check whether the number column is text. After the scanned document is recognized by OCR, the recognized numbers must be checked one by one.
❓ Frequently Asked Questions (FAQ)
1. Will single quotes be included in the cell content?
will not be displayed in the cell, it is just a text prefix. However, when the formula is quoted or copied to other systems, it may be retained as a character.
2. Why does the end of the ID number become 0 after it is converted back to a numerical value?
Excel only retains 15 significant digits, and any excess digits will be set to 0. ID numbers should always be in text format.
3. Is there a way to enter text without single quotes?
Can. Set the column to text format before entering, and then enter directly. The leading 0 will be retained.
4. Why does =A1*1 return #VALUE!?
There may be spaces or invisible characters in the cells, preventing the conversion to numbers. You can use =TRIM(A1)*1 instead, clear the spaces first and then calculate.
📝 Summary
Numbers become text, mostly because the cell is in text format or the content has leading single quotes. When changing back to a numeric value, use error checking, sorting, or multiplying by 1; numbers and numbers longer than 15 digits should remain text. By mastering these judgments, you can avoid detours in two types of issues: adding a single quotation mark to an excel function formula and converting excel regular format to numerical format.
