Forum Discussion
Apostrophes in my spreadsheet :-(
Hi There,
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?
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.
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.
Thank you Dustin-Doucette for your workaround. I have quickly tried it and it works for my purposes.
Much appreciated ...
- This was a life saver. Thanks for the work around.
Hi All,
Great workround from Dustin-Doucette
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
=VALUE(A2)
I then hide all the columns from the form. Each time an entry is returned, it adds the formula to the next row automatically.
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
int(body('Get_response_details')?['r5b82853867004cdfa1f7eecee5d39f60']) where the code number between the [] is from the field in the dynamic content.Rob
Los GallardosMicrosoft Power Automate Community Super User
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...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.
Brian (Ireland)
- Is this w/o using the Number restriction on the question? I've never seen this, but I've always required the answer type be number when I needed a number.
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.
I found this as well but in the forms questions not the results . I resolved it by duplicating the microsoft form. The apostrophes disappeared from the results.
Brian Donnelly Just use power query and your problem is solved
