Forum Discussion
Solved
The dreaded Apostrophe is turning numbers into text in a forms spreadsheet
I answered my first question by checking one of my other sheets, but this one is a bit trickier. When people use MS Forms, even for questions I have added restrictions to so it must be a number, ...
I guess the Form is linked to Excel file hosted on SharePoint site. That issue is known for years, at least two workarounds
1) If you have column [Question 1] which have number as text, you may add to the table additional column Q1 as =@[Question 1]*1, or =CLEAN(@[Question 1])*1, etc and work with these columns. Adding of additional columns into the forms table won't crash the process.
2) Instead of linked table use Power Automate which is triggered by form submit and adds rows to the table in your file. In this case not linked with the Form. Transformation could be done within Power Automate flow.
Davidm54 Following. Crazy that it has been 4 years and the issue persists. Following another discussion that has the same issue.
It makes sense, though, since Forms is just using a text question with "restrictions" on the numeric parts of an alphanumeric string.
What is needed is an actual numeric question type, and then in addition to the current number restrictions (greater than, not between, etc) you could have things like # of digits, allow decimal places, allow/disallow negative numbers, integers only, etc.
What is needed is an actual numeric question type, and then in addition to the current number restrictions (greater than, not between, etc) you could have things like # of digits, allow decimal places, allow/disallow negative numbers, integers only, etc.