Responses changing from number to text

Confirming the issue remains unresolved today (out of the box) on January 24, 2022. BUT, I have a workaround that’s as good as having this issue resolved out of the box!!

I realized the apostrophe is being added when the submission is being processed and recorded to Excel Online.

So how do we get to the data before Excel gets to it? We have to use Power Automate to get the Forms data (which gets it at source), and write the submission as a new row into an excel file. The best way to do this is to copy the existing file and let Power Automate write into that file.

I did this and I was able to capture data without the apostrophe and every time someone submitted the form, power automate processed the submission and kept adding new rows to my new Excel sheet for each submission in the proper number format.

Now that it’s being captured as a numbe, I was able to run pivot tables as needed (sum of values instead of count). I also linked the submissions to up SharePoint List, and connected it to Power BI and it worked perfectly.

If you need help setting this up just go on YouTube and search “add row to an Excel sheet power automate”.

Hope this helps! Good luck everyone