Data Import & Export

How to Fix Microsoft Forms Numeric Responses Stored as Text in Excel

Nimra Malik Oct 7, 2026 868 views

Question details

Users need a way to convert numeric survey responses exported from Microsoft Forms into actual numbers, as they are currently being imported into Excel as text.

Product: Microsoft Excel / Microsoft Forms
Device & OS: not provided
Scenario: Exporting Microsoft Forms response data to Excel for mathematical calculations and data analysis.
Observed behavior: Numeric values from the form responses, particularly the number 1, are imported with a hidden leading apostrophe. Excel interprets these values as text, which breaks calculation formulas such as subtraction or summation.

Before you start

Identify the specific columns containing the imported Microsoft Forms data and ensure you have an empty adjacent column if you plan to use formula-based conversions.

Solution 1Recommended

Convert Text to Numbers Using the VALUE or Double Unary Formula

Use a simple formula to force Excel to evaluate the text-formatted cell as a numeric value, which ignores the hidden apostrophe.

This method is highly effective because functions like VALUE() or the double unary operator (--) are specifically designed to translate text strings that represent numbers into actual computational numbers.

Create a helper column

Insert a new blank column immediately to the right of the column containing your Microsoft Forms numeric responses.

Enter the conversion formula

In the first empty cell of your new column, type the formula =--A2 or =VALUE(A2) (assuming A2 is the cell containing the text number), and press Enter.

Apply formula to the entire column

Click the small square at the bottom-right corner of the cell containing your formula and drag it down to apply the formula to all corresponding rows.

Paste as values

Copy the new calculated column, right-click the original text column, and choose 'Paste Special' > 'Values' to replace the text with actual numbers. You can then delete the helper column.

Convert Text to Numbers Using the VALUE or Double Unary Formula

Calculation Restored: Once converted using the formula, you can seamlessly perform subtractions, additions, and other mathematical calculations on the data.

Solution 2

Remove the Hidden Apostrophe Using Text to Columns

Use Excel's built-in Text to Columns tool to quickly re-evaluate an entire column's data format and strip out the hidden apostrophes.

Solution 3

Use the Error Checking Smart Tag

Leverage Excel's automatic error checking feature to convert text numbers to numeric values with just a couple of clicks.

Data Processing Solution

Easily Convert Text to Numbers Using WPS Spreadsheet

WPS Office offers a highly compatible and intuitive Spreadsheet tool that effortlessly handles imported form data. You can quickly fix text-formatted numbers using built-in formulas, error checking, or the Text to Columns feature, ensuring your Microsoft Forms exports are instantly ready for accurate analysis.

1. Open your Forms export file: Launch WPS Spreadsheet and open the .xlsx file downloaded from Microsoft Forms.
2. Select the text-formatted data: Highlight the column or specific cells where the numbers are stored as text (often indicated by a green corner mark).
3. Convert instantly: Click the yellow alert icon that appears beside your selection and choose 'Convert to Number' to instantly fix the hidden apostrophe issue.

Seamless compatibility with Microsoft Excel (.xlsx) files exported from FormsOne-click 'Convert to Number' feature for bulk processing text-formatted dataFree, lightweight, and easy-to-use alternative to Microsoft Office

Frequently Asked Questions

Why does Microsoft Forms add an apostrophe to my numerical data?

Microsoft Forms occasionally formats data as text to prevent data loss or formatting corruption during export, such as dropping leading zeros. However, this hidden apostrophe forces Excel to read the entry strictly as text, preventing mathematical operations.

Does this apostrophe issue only affect the number 1?

While many users report noticing this issue frequently with the number 1, Microsoft Forms can store any numeric response as text depending on how the original question type was configured and how the export engine parses the characters.

Can I prevent Microsoft Forms from storing numbers as text before exporting?

If you are using a standard 'Text' question in Forms, the output is inherently text. To encourage numerical output, enable 'Restrictions' on your Forms question and set it to 'Number'. However, even with this setting, exported CSV or Excel files may occasionally still require text-to-number conversion depending on the dataset.

Excel & Data Problems