Forum Discussion
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
Monis9001 Hi, I don't know if you'll see this as it's been two years but thank you for your answer.
I've tried doing what you describe with Power Automate but I don't understand what "write the submission as a new row into an excel file" is about.
Can't believe this bug has been existing for 5 years already :')
Tks
- I will just leave this here. https://www.makeuseof.com/sync-microsoft-forms-to-excel/
Please explain how you set this up. I couldn't find a good video on how to. Do you have a suggested video? Monis9001
I'm having the same issue too. I initially proposes using Forms to help use save time with creating Excel sheets but the saved time won't work if I have to clean up the data every time I'm using the Form.
I may use this https://answers.microsoft.com/en-us/msoffice/forum/all/form-is-automatically-storing-numbers-as-text-wont/6e6f3b60-a29c-40a5-ac92-18424ed4a62e where you can convert the columns to values after receiving all responses in your Excel. It's tedious when working with many sheets.
