Data Import & Export

Convert a Column of Text Numbers in Excel Without F2

Tauseeq MagsiTauseeq Magsi Nov 22, 2026 868 views

Question details

Convert approximately 400 numbers stored as text into numeric values without editing each cell individually.

Convert a Column of Text Numbers in Excel Without F2
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.
Before you start

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.

Solution 1Recommended

Convert the column with Text to Columns

Use this method to convert a selected range of numeric text in one operation.

1
Select the values

Select the affected cells in the column, excluding any header.

2
Open Text to Columns

On the Data tab, select Text to Columns, then choose Fixed width and select Next.

3
Remove column breaks

In the preview, remove any vertical break lines by double-clicking them, then select Next.

4
Convert the values

Choose General as the column data format and select Finish. Check that the VLOOKUP formulas now return the expected results.

Convert the column with Text to Columns
Check the values first: General format can change leading zeros and may interpret some entries as dates. Use this method only when the selected values should be numbers.
Convert numeric text in WPS

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. 1. Open the workbook: Open your Excel file in WPS Spreadsheets and select the cells containing numeric text.
  2. 2. Start the conversion: Go to Data and select Text to Columns.
  3. 3. Keep the values together: Choose Fixed width, continue through the wizard, and remove any unwanted column breaks in the preview.
  4. 4. Finish and check: Choose General as the data format, select Finish, and check the VLOOKUP results.
Convert a column of numeric text without editing every cellWork with VLOOKUP formulas in a familiar spreadsheet interfaceOpen and edit Microsoft Excel XLS and XLSX filesUse WPS Office for free
microsoft office alternative - wps office

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.