Forum Discussion
Responses changing from number to text
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.