Forum Discussion
Responses changing from number to text
I've set up a survey in Forms to be used to count attendance for an organization on a weekly basis. There are 7 different questions to input a number for the count in different sections. I've set the restrictions on all the questions, and when I pull up the survey on my phone browser I can see the answer box says "The value must be a number." But when the answers are submitted, they are being changed to text; the Excel spreadsheet shows the answer is '59, instead of 59. The cell format also shows that it should be number.
The problem is that I have some formulas built in the Excel spreadsheet recording the answers, and they obviously cannot work if the answers are text. Is anyone else dealing with this problem? Any ideas how to fix it?
dfox74 3.5 years later, just walked into the same issue you and everyone else on this thread is having... any chance of a formal fix, MS?
I'm trying to do some (dynamic) statistical analysis on the numbers entered, and the only values that are included are the ones we've manually corrected (because those ARE numbers, but the unedited numbers are all stored as text and therefore ignored by min/max/average etc.). Very frustrating. I have null values that I don't want to convert into zeroes using forced type conversion on every cell. I just want the actual numbers to be stored in Excel as numbers.
I agree with Star_D that there should be an actual number type question, instead of text with numerical restrictions. My use case has about 40 columns of primarily numerical data, once the form responses reach Excel, plus some automatically calculated columns, which work ok with some forced type conversions in the calculations. I don't see changing to Power Automate to fix the forced text format problem at this point - we've accumulated about 10,000 rows of manual form entries at this point and we have people looking at visualizations of the Excel data live in a connected Power BI report. It's a living process that I'd prefer to avoid interrupting.
I created a separate worksheet in the Excel workbook to perform some statistical analysis on the table columns - to help see central tendencies, outliers and distribution of raw responses. My calculated columns are all forced numeric, so stats on those are not a problem. But the only thing I have to touch periodically which isn't automated is the type corrections on the raw data, so that all the numbers will be included in the statistical/aggregate functions (which ignore numbers formatted as text). Because some entries are optional depending on branching, I can't do a simple forced conversion on the whole table like paste add zero or paste multiply by 1. I suppose I could create additional "preprocessed" columns where the only math performed was logical (if <>"") type conversion, and then run statistics on those. Seems like a lot of effort that could be avoided if we just had a numerical question type.
- This make sense when you think about it (but doesn't stop it from being annoying): the only question type I know of in MS Forms that has "restrictions" in the more settings is the Text question. The restriction is specifying that you can only use the numeric part of an alphanumeric answer, BUT it is still a TEXT question, and it is formatted as such in Excel.
What is needed in Forms is a actual Number question type, not a modified Text question tpe.
If there were an actual number type question in MS Forms than we could have actual number formatting options like # of decimal places, Integers only, currency, # of digits (as opposed to using Restrictions > Between), etc.
Again, not saying I approve of it...but if you remember that a "TEXT question with restrictions" is still a "TEXT question" at its core, then it makes sense. 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 everyoneMonis9001 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/
dfox74 I just started to work in Forms and encountered this issue. Disappointing to see it is an outstanding issue for so long. I am one of the end users that push to use the tools like Forms and Teams in our organization, but it seems every time I get some traction with colleagues we encounter something goofy like this. My colleagues give up "too complicated" and instead say things like "Trello can do it..." Help me help you!
Nous sommes en 2022... et le problème n'est toujours pas réglé?
Pourquoi restreindre la réponse dans le formulaire à un nombre si ensuite dans le Excel, ce n'est pas un nombre... aberrant.
It's 2021, and the same issue still happening
- Still have the issue. I worked on my simple Monthly Expense and came across the issue. Thought it was a typo on my end. The issue I found are both number to text and additional ( ' ).
I couldn't believe it has been an issue for so long and not officially fixed yet.
I guess I have to do the CLEAN maneuver for the time being, thanks for the advice anyways.Thank you for this discussion, I have the same issue and it's work with the = * 1 formula, but still hope Microsoft will fix officially it soon.
- Wenjun Gong - Any update to this issue?
We would like to export Form responses to Excel then use a PivotTable to analyze the results but cannot without cleaning the data from text to number (as others have mentioned). Our surveymonkey export works much better.
It's possible the Form was created with text fields, then edited to add the number requirement later. Maybe that will help your team reproduce and fix??Unfortunately, creating a new survey is not ideal solution, the link has already been shared. dfox74 I see this issue is from 2019 and now in 2021 it seems still not fixed. Do we really have to work with a workaround?
My workaround was to create new columns off to the side of my table and use
= targetcolumn * 1It worked fine but agree this shouldn't need to be done!
Wenjun GongMicrosoft
dfox74 thanks for reporting this issue. We will have our engineers to take a look.
- This issue is being commented on for over a year now? What are your engineers saying?
Thanks.
It's October 2020 and I do have the same problem, CLEAN step from Movaan is a good solution, however #Microsoft should be aware of this and solve this issue.
