Forum Discussion
Apostrophes in my spreadsheet :-(
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.
