Convert a Column of Text Numbers in Excel Without F2
Question details
Convert approximately 400 numbers stored as text into numeric values without editing each cell individually.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A column of values is used in VLOOKUP formulas, but the values need to be converted in bulk.
- Observed behavior
- VLOOKUP does not match the values as expected; pressing F2 and Enter on each affected cell makes the lookup work.
Check whether the lookup value and the VLOOKUP lookup column use the same data type. If the values are identifiers with leading zeros, keep them as text rather than converting them to numbers.
Convert the column with Text to Columns
Use this method to convert a selected range of numeric text in one operation.
Select the affected cells in the column, excluding any header.
On the Data tab, select Text to Columns, then choose Fixed width and select Next.
In the preview, remove any vertical break lines by double-clicking them, then select Next.
Choose General as the column data format and select Finish. Check that the VLOOKUP formulas now return the expected results.

Multiply the selected values by one
Use Paste Special to convert numeric text in place without editing each cell.
Convert Text Numbers in WPS Spreadsheets
Use Text to Columns in WPS Spreadsheets to convert a range of numeric text to numbers, then check that your VLOOKUP values and lookup column use matching types.
- 1. Open the workbook: Open your Excel file in WPS Spreadsheets and select the cells containing numeric text.
- 2. Start the conversion: Go to Data and select Text to Columns.
- 3. Keep the values together: Choose Fixed width, continue through the wizard, and remove any unwanted column breaks in the preview.
- 4. Finish and check: Choose General as the data format, select Finish, and check the VLOOKUP results.

Frequently Asked Questions
Why does VLOOKUP fail when the numbers look identical?
One value may be a number while the other is stored as text. Check both the lookup value and the first column of the lookup range, and convert them to the same type.
Will Paste Special Values convert text numbers?
Usually not. In Excel, copy a cell containing 1, select the affected cells, and use Paste Special > Multiply to convert numeric text.
How can I preserve leading zeros in lookup values?
Keep the identifiers as text on both sides of the lookup. Converting them to numbers removes their leading zeros.
What if Text to Columns does not fix every value?
Check for spaces or other nonnumeric characters in the affected cells. Clean those characters, then retry Data > Text to Columns or use a formula to produce matching values.




