Jan 30 2020 06:32 AM
Jan 30 2020 06:32 AM
I have found apostrophes within the Excel Spreadsheet generated within the Responses from a MS Form.
if I create a Form asking for a number to be returned, lets say I am asking for respondents to tell me how many Kms they traveled. This number is entered into the background spreadsheet with a leading Apostrophe :( this means I have to remove apostrophes before I can manipulate the result data. THIS IS A PAIN!
Can we have the forms set up to not default to returning a text item, because this is why this is happening.
C'mon Microsoft ..... whats going on?
Jan 31 2020 10:07 AM
Hi Brian (@Brian Donnelly ), there isn't a number field in Forms so it needs to use a text field. Of course the way that Excel handles text is to add the apostrophe.
But you can get over this with Power Automate. If you save the response to a SharePoint list you can have a number column for the number in the form and just convert it in PA to an integer.
The Compose uses the sytax
Microsoft Power Automate Community Super User
Feb 03 2020 05:51 AM
@RobElliott I too am having this issue now... I did not have it before!
I think it's crazy that I can put some validation (restrictions) on a field on my form only for the format not to be respected??
Seems like Microsoft are really missing a trick. This is easy to accomplish in Google sheets and their version of forms...
We should be able to achieve basic functionality like this without the need of yet ANOTHER Microsoft product!
I already use SharePoint and Microsoft flows to allow more than 1 form to populate the same workbook... configuring that was enough of a pain without this now too...
Feb 03 2020 06:10 AM
Agreed @Pete_McMorrow it is a pain. My employer forces me use the MS Suite, as we have an agreement with Microsoft and I'm sure a huge a huge amount of background processes that I'm not even aware of.
This 'little' task is just a tiny item I used to use 'Google Forms' to do and it used to work so easily. Now you can see the response from @RobElliott (which I must acknowledge thanks to, for his input) But look at the amount of hoops to jump through just to get MS Forms to accept a number format into a background spreadsheet...
Thank you both for your input.
Jun 03 2020 06:24 AMSolution
Hi, I know this is a few months old but I've just recently encountered this while working on a project myself. I've found a reasonable workaround within the spreadsheet itself, as I want to avoid more intensive work-arounds that may need to be replicated by others as much as possible.
When your form dumps data into your responses sheet, the numbers will come in with the apostrophe and treated as text as you've seen. If you add a column at the end of your spreadsheet, give it a header that makes sense for your situation, and in your first column use formula Value([@[FormResponseField]])
This will convert that text field to a number, and since forms will only allow numbers you should not encounter any errors down the road. You can then reference your newly created field in your other formulas.
So let's say your number question on forms was "How many cookies can you eat?", you add a column at the end of your table that has header"#CookiesEat", and your formula in the first row would become "=Value([@[How many cookies can you eat?]])" which would convert to value, and in subsequent formulas if you need to sum that column for example you can reference the table and column using "Sum(Table1[#CookiesEat])". The table is named Table1 by default but can be changed.
Sorry for the wordy post! I don't know an easier way to explain and am new to posting advice :)
Hope this can help you or anyone else in our shoes that comes across this down the road.
Jul 27 2021 01:54 PM
Jul 27 2021 01:57 PM
Hi! Yes this is including when you ask for a number and require the response to be a number. Despite requiring the response to be a number, Microsoft dumps the data into their spreadsheet as text, which is why I had to create the Value() workaround.
Feb 16 2022 02:24 AM
Great workround from @DustinDoucette
I have simply added additional columns to the spreadsheet and as an example - to convert the returned form result of '80 in Cell A2 as a number used the formula
I then hide all the columns from the form. Each time an entry is returned, it adds the formula to the next row automatically.