SOLVED

Apostrophes in my spreadsheet :-(

%3CLINGO-SUB%20id%3D%22lingo-sub-1139819%22%20slang%3D%22en-US%22%3EApostrophes%20in%20my%20spreadsheet%20%3A(%3C%2Fimg%3E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1139819%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20There%2C%3C%2FP%3E%3CP%3EI%20have%20found%20apostrophes%20within%20the%20Excel%20Spreadsheet%20generated%20within%20the%20Responses%20from%20a%20MS%20Form.%3C%2FP%3E%3CP%3Eif%20I%20create%20a%20Form%20asking%20for%20a%20number%20to%20be%20returned%2C%20lets%20say%20I%20am%20asking%20for%20respondents%20to%20tell%20me%20how%20many%20Kms%20they%20traveled.%26nbsp%3B%20This%20number%20is%20entered%20into%20the%20background%20spreadsheet%20with%20a%20leading%26nbsp%3BApostrophe%20%3A(%3C%2Fimg%3E%20this%20means%20I%20have%20to%20remove%20apostrophes%20before%20I%20can%20manipulate%20the%20result%20data.%26nbsp%3B%20THIS%20IS%20A%20PAIN!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20we%20have%20the%20forms%20set%20up%20to%20not%20default%20to%20returning%20a%20text%20item%2C%20because%20this%20is%20why%20this%20is%20happening.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EC'mon%20Microsoft%20.....%20whats%20going%20on%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1139819%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EForm%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EForms%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMicrosoft%20Forms%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EResponses%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESurvey%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1144379%22%20slang%3D%22en-US%22%3ERe%3A%20Apostrophes%20in%20my%20spreadsheet%20%3A(%3C%2Fimg%3E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1144379%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Brian%20(%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F150194%22%20target%3D%22_blank%22%3E%40Brian%20Donnelly%3C%2FA%3E%26nbsp%3B)%2C%20there%20isn't%20a%20number%20field%20in%20Forms%20so%20it%20needs%20to%20use%20a%20text%20field.%20Of%20course%20the%20way%20that%20Excel%20handles%20text%20is%20to%20add%20the%20apostrophe.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20you%20can%20get%20over%20this%20with%20Power%20Automate.%20If%20you%20save%20the%20response%20to%20a%20SharePoint%20list%20you%20can%20have%20a%20number%20column%20for%20the%20number%20in%20the%20form%20and%20just%20convert%20it%20in%20PA%20to%20an%20integer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20604px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F168355i0E8D9279CD1D855B%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22excelNumber.png%22%20title%3D%22excelNumber.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Compose%20uses%20the%20sytax%26nbsp%3B%3C%2FP%3E%3CDIV%3E%3CDIV%3E%3CSTRONG%3Eint(body('Get_response_details')%3F%5B'r5b82853867004cdfa1f7eecee5d39f60'%5D)%26nbsp%3B%3C%2FSTRONG%3Ewhere%20the%20code%20number%20between%20the%20%5B%5D%20is%20from%20the%20field%20in%20the%20dynamic%20content.%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3C%2FP%3E%3CP%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1147748%22%20slang%3D%22en-US%22%3ERe%3A%20Apostrophes%20in%20my%20spreadsheet%20%3A(%3C%2Fimg%3E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1147748%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%20I%20too%20am%20having%20this%20issue%20now...%20I%20did%20not%20have%20it%20before!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20think%20it's%20crazy%20that%20I%20can%20put%20some%20validation%20(restrictions)%20on%20a%20field%20on%20my%20form%20only%20for%20the%20format%20not%20to%20be%20respected%3F%3F%3CBR%20%2F%3E%3CBR%20%2F%3ESeems%20like%20Microsoft%20are%20really%20missing%20a%20trick.%20This%20is%20easy%20to%20accomplish%20in%20Google%20sheets%20and%20their%20version%20of%20forms...%3CBR%20%2F%3E%3CBR%20%2F%3EWe%20should%20be%20able%20to%20achieve%20basic%20functionality%20like%20this%20without%20the%20need%20of%20yet%20ANOTHER%20Microsoft%20product!%3CBR%20%2F%3E%3CBR%20%2F%3EI%20already%20use%20SharePoint%20and%20Microsoft%20flows%20to%20allow%20more%20than%201%20form%20to%20populate%20the%20same%20workbook...%20configuring%20that%20was%20enough%20of%20a%20pain%20without%20this%20now%20too...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1147784%22%20slang%3D%22en-US%22%3ERe%3A%20Apostrophes%20in%20my%20spreadsheet%20%3A(%3C%2Fimg%3E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1147784%22%20slang%3D%22en-US%22%3E%3CP%3EAgreed%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F543049%22%20target%3D%22_blank%22%3E%40Pete_McMorrow%3C%2FA%3E%26nbsp%3Bit%20is%20a%20pain.%26nbsp%3B%20My%20employer%20forces%20me%20use%20the%20MS%20Suite%2C%20as%20we%20have%20an%20agreement%20with%20Microsoft%20and%20I'm%20sure%20a%20huge%20a%20huge%20amount%20of%20background%20processes%20that%20I'm%20not%20even%20aware%20of.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20'little'%20task%20is%20just%20a%20tiny%20item%20I%20used%20to%20use%20'Google%20Forms'%20to%20do%20and%20it%20used%20to%20work%20so%20easily.%26nbsp%3B%20Now%20you%20can%20see%20the%20response%20from%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40Rob%20Elliott%3C%2FA%3E%26nbsp%3B%20(which%20I%20must%20acknowledge%20thanks%20to%2C%20for%20his%20input)%20But%20look%20at%20the%20amount%20of%20hoops%20to%20jump%20through%20just%20to%20get%20MS%20Forms%20to%20accept%20a%20number%20format%20into%20a%20background%20spreadsheet...%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20both%20for%20your%20input.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBrian%20(Ireland)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436158%22%20slang%3D%22en-US%22%3ERe%3A%20Apostrophes%20in%20my%20spreadsheet%20%3A(%3C%2Fimg%3E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F150194%22%20target%3D%22_blank%22%3E%40Brian%20Donnelly%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F543049%22%20target%3D%22_blank%22%3E%40Pete_McMorrow%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20I%20know%20this%20is%20a%20few%20months%20old%20but%20I've%20just%20recently%20encountered%20this%20while%20working%20on%20a%20project%20myself.%20I've%20found%20a%20reasonable%20workaround%20within%20the%20spreadsheet%20itself%2C%20as%20I%20want%20to%20avoid%20more%20intensive%20work-arounds%20that%20may%20need%20to%20be%20replicated%20by%20others%20as%20much%20as%20possible.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20your%20form%20dumps%20data%20into%20your%20responses%20sheet%2C%20the%20numbers%20will%20come%20in%20with%20the%20apostrophe%20and%20treated%20as%20text%20as%20you've%20seen.%20If%20you%20add%20a%20column%20at%20the%20end%20of%20your%20spreadsheet%2C%20give%20it%20a%20header%20that%20makes%20sense%20for%20your%20situation%2C%20and%20in%20your%20first%20column%20use%20formula%20Value(%5B%40%5BFormResponseField%5D%5D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20convert%20that%20text%20field%20to%20a%20number%2C%20and%20since%20forms%20will%20only%20allow%20numbers%20you%20should%20not%20encounter%20any%20errors%20down%20the%20road.%20You%20can%20then%20reference%20your%20newly%20created%20field%20in%20your%20other%20formulas.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20let's%20say%20your%20number%20question%20on%20forms%20was%20%22How%20many%20cookies%20can%20you%20eat%3F%22%2C%20you%20add%20a%20column%20at%20the%20end%20of%20your%20table%20that%20has%20header%22%23CookiesEat%22%2C%20and%20your%20formula%20in%20the%20first%20row%20would%20become%20%22%3DValue(%5B%40%5BHow%20many%20cookies%20can%20you%20eat%3F%5D%5D)%22%20which%20would%20convert%20to%20value%2C%20and%20in%20subsequent%20formulas%20if%20you%20need%20to%20sum%20that%20column%20for%20example%20you%20can%20reference%20the%20table%20and%20column%20using%20%22Sum(Table1%5B%23CookiesEat%5D)%22.%20The%20table%20is%20named%20Table1%20by%20default%20but%20can%20be%20changed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20for%20the%20wordy%20post!%20I%20don't%20know%20an%20easier%20way%20to%20explain%20and%20am%20new%20to%20posting%20advice%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20can%20help%20you%20or%20anyone%20else%20in%20our%20shoes%20that%20comes%20across%20this%20down%20the%20road.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1436381%22%20slang%3D%22en-US%22%3ERe%3A%20Apostrophes%20in%20my%20spreadsheet%20%3A(%3C%2Fimg%3E%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1436381%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F687774%22%20target%3D%22_blank%22%3E%40DustinDoucette%3C%2FA%3E%26nbsp%3Bfor%20your%20workaround.%26nbsp%3B%20I%20have%20quickly%20tried%20it%20and%20it%20works%20for%20my%20purposes.%3C%2FP%3E%3CP%3EMuch%20appreciated%20...%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

5 Replies
Highlighted

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.

 

excelNumber.png

 

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 Gallardos

Microsoft Power Automate Community Super User

Highlighted

@Rob Elliott  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...

 

Highlighted

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 @Rob Elliott  (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)

 

 

Solution

@Brian Donnelly 

@Pete_McMorrow 

 

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.

Highlighted

Thank you @DustinDoucette for your workaround.  I have quickly tried it and it works for my purposes.

Much appreciated ...