Home

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
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?

3 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)

 

 

Related Conversations
Loading spreadsheet with notes in cells into Teams
DaleA123 in Microsoft Teams on
0 Replies
Spreadsheets with mobile access
darciwilliams in Microsoft Teams on
5 Replies
excell spreadsheet
lonniemc in Excel on
0 Replies
Link in Teams spreadsheet wont open in Teams
Ben Curran in Microsoft Teams on
0 Replies