Data Import & Export

How to Convert Microsoft Forms Exported Numbers Stored as Text in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

Users need a way to convert numerical data exported from Microsoft Forms that appears as text with a leading apostrophe into usable numbers for mathematical calculations.

Product: Excel
Device & OS: not provided
Scenario: Exporting numerical survey or quiz responses from Microsoft Forms to an Excel spreadsheet for data analysis.
Observed behavior: Exported numbers contain a leading apostrophe, causing Excel to store them as text and mathematical formulas (like SUM) to return a value of zero.

Before you start

Before applying bulk data format conversions, verify if any columns genuinely require text formatting (such as ZIP codes or phone numbers with leading zeros).

Solution 1Recommended

Convert Text to Numbers Using Text to Columns

The most efficient and recommended way to change values with leading apostrophes back to usable numbers without creating extra columns.

Using the apostrophe as a delimiter might inadvertently separate data and create unnecessary new columns. The 'Text to Columns' tool can instantly refresh the formatting of the existing column without splitting the data, easily converting the text back into numbers.

Select the Data Range

Highlight all the cells containing the numbers stored as text (the cells displaying leading apostrophes).

Open Text to Columns

Navigate to the 'Data' tab on the top ribbon and click on the 'Text to Columns' button.

Choose No Delimiters

Progress through the wizard steps ensuring that no delimiters are selected so the data remains intact in its current column.

Finish Conversion

Click 'Finish' to complete the process. Your text values will instantly refresh and be converted into calculable numbers.

Future Form Submissions: To prevent this issue in future forms, use a text question type but restrict the input to 'Number' where appropriate.

Solution 2

Convert Using the Paste Special Multiply Method

A quick mathematical workaround using 'Paste Special' to force Excel to recognize the text cells as numerical values.

Efficient Spreadsheet Management

Process Exported Form Data Seamlessly in WPS Spreadsheet

WPS Spreadsheet offers powerful data handling tools, including Text to Columns, to quickly clean and calculate your exported form responses without hassle.

1. Open Exported File: Launch WPS Spreadsheet and open the Excel file you exported from Microsoft Forms.
2. Select Affected Cells: Highlight the specific column containing the numbers stored as text.
3. Apply Text to Columns: Go to the Data tab, click 'Text to Columns', and click Finish directly in the wizard to convert the formats.
4. Verify Calculations: Use the SUM function at the bottom of the column to ensure your numbers are now calculating correctly.

Quickly convert text to numbers for accurate formula calculationsFully compatible with Microsoft Excel (.xlsx) data exportsFree and lightweight alternative for daily data analysisIntuitive interface for seamless data import and export

microsoft office alternative - wps office

Frequently Asked Questions

Why does Microsoft Forms add an apostrophe to my numbers?

Microsoft Forms sometimes adds a leading apostrophe during export to preserve the exact formatting of the user's input, preventing spreadsheet software from automatically removing leading zeros (commonly found in phone numbers or IDs).

How can I stop Forms from exporting numbers as text in the future?

In your Microsoft Forms setup, ensure you use a Text question, click the three dots for more settings, select 'Restrictions', and set the restriction to 'Number'. This enforces proper numeric validation at the point of entry.

Why is my SUM formula returning zero?

If your numbers are stored as text (often indicated by a leading apostrophe or a green triangle in the upper corner of the cell), Excel's SUM formula ignores them, resulting in a zero calculation.

Will using Text to Columns delete my original data?

No. As long as you do not select any delimiters (like comma or space) in the Text to Columns wizard, the data will simply refresh its formatting in its current column without deleting or overwriting adjacent cells.

Excel & Data Problems