Converting numbers to text in Excel is one way to resolve VLOOKUP errors. You can also convert values within a formula using the VALUE function, but this article explains how to use the “Text to Columns” feature to convert data formatting from numbers to text.
How to Convert Numbers to Text in Excel
There are many ways to convert Excel data formats, but using Text to Columns is the only method that can convert the format without shifting the data.
The data is shown below. The values on the left are all numbers, while the values in the table on the right have been converted to text format using “Text to Columns.”
After selecting the numeric data you want to convert to text format, select “Text to Columns” on the ribbon.
In Step 1 below, select “Next.”
In Step 2, select “Next” again.
In Step 3 of Excel’s “Text to Columns” feature, select “Text” under Column data format to convert the numbers to text.
Conclusion
In addition to using “Text to Columns” to convert numbers to text in Excel, you can use the VALUE and TEXT functions within formulas without converting the original data.
See the links below for these methods.
Resolve Excel number-to-text conversion within a formula
- TEXT function: The TEXT function is one of the most commonly used Excel functions for converting numbers to text. It lets you display numbers in a variety of formats.
- VALUE function: The VALUE function converts text strings to numbers, so it can be used to convert a numeric string to a number.
- FORMAT function: The FORMAT function converts numbers to text strings in a specified format. It lets you display numbers in a variety of formats.
- CONCATENATE function: The CONCATENATE function combines text strings. You can use it to combine numbers and text into a text string.
- SUBSTITUTE function: The SUBSTITUTE function replaces specific text with other text. You can use it to replace numbers with text.
- LEFT, RIGHT, and MID functions: The LEFT, RIGHT, and MID functions extract the left, right, or middle part of a text string. You can use them to extract numbers as text strings.
- REPLACE function: The REPLACE function replaces part of a text string with other text. You can use it to replace numbers with text.
- LEN function: The LEN function returns the length of a text string. You can use it to calculate the length needed to convert numbers to text strings.
- IF, AND, and OR functions: The IF, AND, and OR functions handle conditional logic. You can use these functions to create conditional logic for converting numbers to text strings.