Microsoft Form Adding a Hidden Apostrophe to Numerical Data

Question

I am using a Microsoft Form to capture some basic inventory data (quantities received vs quantities consumed) with a simple formula to keep a running total of the items remaining in inventory. The problem I am having is that a hidden apostrophe is being added to the quantities (both received and consumed) causing the formula not to work correctly resulting in incorrect quantities for the remaining inventory. I can go into the spreadsheet and remove the apostrophe, but the issue continues. Any solutions to keep this from happening so I don't have to keep going into the spreadsheet and making the needed correction?

Answer 1

It is limitation in Form Response. It will convert number in to text with apostrophe. You may need to remove it manually.

Answer 2

Could you share us your formula?

You may use double "-" to convert text number to value which can be used in formula.

Answer 3

The hidden apostrophe seems to occur when the quantity is 1 (I have already removed the apostrophe in this example).

The data in the Form Responses tab is pivoted out with the Items Remaining totals maintained with subtraction. The issue for me is when the apostrophe is added to the Items Received and Items Consumed totals on the Form Responses tab, Excel sees it as text instead of a number so the formula does not calculate the totals correctly.