integer input is formatted as string

%3CLINGO-SUB%20id%3D%22lingo-sub-810678%22%20slang%3D%22en-US%22%3Einteger%20input%20is%20formatted%20as%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810678%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20collecting%20integers%20greater%20than%20zero%2C%20but%20entries%20are%20prepended%20by%20a%20single%20apostrophe%20in%20excel.%3C%2FP%3E%3CP%3EIs%20this%20not%20a%20bug%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20281px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F127451iB50DF88D91E1FA00%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22screenshot.png%22%20title%3D%22screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-810678%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EForms%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eissue%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-810765%22%20slang%3D%22en-US%22%3ERE%3A%20integer%20input%20is%20formatted%20as%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-810765%22%20slang%3D%22en-US%22%3EI%20have%20the%20same%20issue.%20I%20want%20to%20present%20the%20collected%20data%20in%20a%20pivot%20chart.%20When%20I%20manually%20convert%20the%20data%20to%20number%20in%20each%20cell%20then%20the%20chart%20works.%20Must%20be%20some%20way%20to%20change%20the%20format%20of%20data%20when%20exported%20to%20Excel.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814172%22%20slang%3D%22en-US%22%3ERe%3A%20RE%3A%20integer%20input%20is%20formatted%20as%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814172%22%20slang%3D%22en-US%22%3EAgreed%20this%20is%20a%20headache.%20Hopefully%20it's%20fixed%20soon.%3CBR%20%2F%3EIn%20the%20meantime%2C%20this%20link%20has%20has%20some%20workarounds.%20Text%20to%20Column%20works%20well.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F2868-excel-remove-leading-apostrophe.html%23a1%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2Fdocuments%2Fexcel%2F2868-excel-remove-leading-apostrophe.html%23a1%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-814520%22%20slang%3D%22en-US%22%3ERe%3A%20integer%20input%20is%20formatted%20as%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-814520%22%20slang%3D%22en-US%22%3E%3CP%3ETo%20get%20rid%20of%20this%20issue%20need%20to%20apply%20the%20formula%20that%20is%20B3%26amp%3B%22%3A%22%26amp%3BC3.%3C%2FP%3E%3CP%3EThis%20will%20not%20allow%20the%20integer%20to%20be%20formatted%20as%20a%20string.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822674%22%20slang%3D%22en-US%22%3ERe%3A%20integer%20input%20is%20formatted%20as%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822674%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89804%22%20target%3D%22_blank%22%3E%40Anders%20Krogsager%3C%2FA%3E%26nbsp%3B%26nbsp%3Bi%20have%20the%20same%20issue%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-865358%22%20slang%3D%22en-US%22%3ERe%3A%20integer%20input%20is%20formatted%20as%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-865358%22%20slang%3D%22en-US%22%3E%3CP%3ESame%20issue.%20Using%20for%20business.%20Not%20ideal.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89804%22%20target%3D%22_blank%22%3E%40Anders%20Krogsager%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871768%22%20slang%3D%22en-US%22%3ERE%3A%20integer%20input%20is%20formatted%20as%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871768%22%20slang%3D%22en-US%22%3EThis%20is%20new%20behaviour%20that%20wasn't%20present%20a%20couple%20of%20months%20ago%2C%20I%20hope%20this%20is%20fixed%20soon.%20I%20import%20the%20values%20from%20the%20forms%20into%20access%2C%20and%20it%20crashes%20on%20the%20text%20fields%20that%20are%20supposed%20to%20be%20integers%20if%20I%20don't%20manually%20convert%20them%20first.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-916601%22%20slang%3D%22en-US%22%3ERe%3A%20integer%20input%20is%20formatted%20as%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-916601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F89804%22%20target%3D%22_blank%22%3E%40Anders%20Krogsager%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20problem%3A%20the%20excel%20form%20generated%20my%20MS%20Forms%2C%20put%20an%20apostrophe%20before%20the%20number%20or%20text...so%20the%20pivot%20doesn't%20work...%20is%20it%20a%20bug%3F%20started%20few%20weeks%20ago!!%3C%2FP%3E%3CP%3E%26nbsp%3B%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%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F137851i8FF0EC5DE4DD3E2D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22msforms.JPG%22%20title%3D%22msforms.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I am collecting integers greater than zero, but entries are prepended by a single apostrophe in excel.

Is this not a bug?

screenshot.png

7 Replies
I have the same issue. I want to present the collected data in a pivot chart. When I manually convert the data to number in each cell then the chart works. Must be some way to change the format of data when exported to Excel.
Agreed this is a headache. Hopefully it's fixed soon.
In the meantime, this link has has some workarounds. Text to Column works well.

https://www.extendoffice.com/documents/excel/2868-excel-remove-leading-apostrophe.html#a1

To get rid of this issue need to apply the formula that is B3&":"&C3.

This will not allow the integer to be formatted as a string.

@Anders Krogsager  i have the same issue

Same issue. Using for business. Not ideal. @Anders Krogsager 

This is new behaviour that wasn't present a couple of months ago, I hope this is fixed soon. I import the values from the forms into access, and it crashes on the text fields that are supposed to be integers if I don't manually convert them first.

@Anders Krogsager 

I have a problem: the excel form generated my MS Forms, put an apostrophe before the number or text...so the pivot doesn't work... is it a bug? started few weeks ago!!

 

 

msforms.JPG