The dreaded Apostrophe is turning numbers into text in a forms spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-2110643%22%20slang%3D%22en-US%22%3EThe%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2110643%22%20slang%3D%22en-US%22%3E%3CP%3EI%20answered%20my%20first%20question%20by%20checking%20one%20of%20my%20other%20sheets%2C%20but%20this%20one%20is%20a%20bit%20trickier.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20people%20use%20MS%20Forms%2C%20even%20for%20questions%20I%20have%20added%20restrictions%20to%20so%20it%20must%20be%20a%20number%2C%20in%20the%20associated%20spreadsheet%2C%20the%20data%20is%20presented%20as%20text%2C%20specifically%3A%20'2%2C%20'3%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20obviously%20then%20limits%20options%20as%20far%20as%20what%20can%20be%20done%20with%20the%20data%2C%20conditional%20formatting%2C%20calculations%2C%20etc%2C%20all%20go%20out%20the%20window.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DTsqrVAdWRfE%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DTsqrVAdWRfE%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20vid%20has%20a%20serviceable%20couple%20of%20solutions%20if%20it%20was%20a%20one%20off%20problem%2C%20however%2C%20this%20is%20a%20table%20being%20updated%20from%20form%20responses%2C%20and%20I'd%20like%20to%20remove%20the%20apostrophes%20as%20soon%20as%20new%20data%20is%20in%20there%2C%20automatically.%20(Regular%20formatting%20doesn't%20do%20the%20job.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E(I%20could%20not%20get%20the%20VBA%20solution%20offered%20in%20below%20video%20to%20work%2C%20unsure%20if%20I%20messed%20up%2C%20or%20if%20the%20fact%20it's%20dealing%20with%20a%20table%20makes%20a%20difference.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHelp%20please!%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2110643%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2113398%22%20slang%3D%22en-US%22%3ERe%3A%20The%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2113398%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949996%22%20target%3D%22_blank%22%3E%40Davidm54%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20always%20better%20to%20split%20Excel%20workbook%20on%203%20zones%3C%2FP%3E%0A%3CP%3E-%20source%2Fraw%20data%3C%2FP%3E%0A%3CP%3E-%20data%20preparation%3C%2FP%3E%0A%3CP%3E-%20reporting%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%20is%20filled%20automatically%20by%20SharePoint%20app%20triggered%20by%20Forms%3C%2FP%3E%0A%3CP%3E-%20in%20second%20you%20may%20generate%20any%20table%20and%20perform%20any%20calculation%20to%20transform%20data%3C%2FP%3E%0A%3CP%3E-%20in%20third%20could%20be%20PivotTables%20%2F%20Charts%20based%20on%20second%20part.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2112540%22%20slang%3D%22en-US%22%3ERe%3A%20The%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2112540%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949996%22%20target%3D%22_blank%22%3E%40Davidm54%3C%2FA%3E%26nbsp%3BFor%20reference%2C%20you%20can%20also%20do%20the%20following%20(%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fexcel%2Fpreserve-cell-formatting%2Fm-p%2F2000590%2Fhighlight%2Ftrue%23M83999%22%20target%3D%22_self%22%3Esource%3C%2FA%3E%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20remove%20single%20quote%20in%20front%20of%20numbers%20in%20a%20cell%2C%20you%20can%20highlight%20the%20entire%20column%20where%20the%20pasted%20text%20figure%20will%20be%20and%20Clear%20Formatting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EGo%20to%20Home%20Tab%20%26gt%3B%20Editing%20Group%26gt%3B%20Clear%20%26gt%3B%20Clear%20Formatting%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-12-23%2010_05_43-.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F251811i472BDCD87A576267%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%222020-12-23%2010_05_43-.png%22%20alt%3D%222020-12-23%2010_05_43-.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2112157%22%20slang%3D%22en-US%22%3ERe%3A%20The%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2112157%22%20slang%3D%22en-US%22%3EThanks.%20Re%201.%20That's%20good%20to%20know%20re%20extra%20columns%2C%20I've%20added%20columns%20at%20the%20end%20on%20form%20response%20sheets%2C%20but%20haven't%20been%20game%20to%20add%20columns%20in%20the%20middle.%3CBR%20%2F%3E%3CBR%20%2F%3E2.%20That%20is%20the%20work%20around%20I%20ended%20up%20with%20this%20time.%20It's%20worked%20well%2C%20as%20with%20numbers%20in%20place%2C%20everything%20flows%20pretty%20easily%20from%20that.%20Far%20less%20reliance%20on%20formulas%20for%20what%20we%20need%20now%2C%20just%20a%20couple%20of%20pivots%20and%20some%20sheets%20referencing%20other%20sheets%20and%20splicers%20to%20allow%20folks%20to%20glance%20at%20what%20they%20want.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2112153%22%20slang%3D%22en-US%22%3ERe%3A%20The%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2112153%22%20slang%3D%22en-US%22%3EThanks.%20I'll%20definitely%20look%20at%20using%20that%2C%20this%20won't%20be%20the%20last%20time%20forms%20is%20being%20used.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2110833%22%20slang%3D%22en-US%22%3ERe%3A%20The%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2110833%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949996%22%20target%3D%22_blank%22%3E%40Davidm54%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20guess%20the%20Form%20is%20linked%20to%20Excel%20file%20hosted%20on%20SharePoint%20site.%20That%20issue%20is%20known%20for%20years%2C%20at%20least%20two%20workarounds%3C%2FP%3E%0A%3CP%3E1)%20If%20you%20have%20column%20%5BQuestion%201%5D%20which%20have%20number%20as%20text%2C%20you%20may%20add%20to%20the%20table%20additional%20column%20Q1%20as%20%3D%3CSPAN%3E%40%5BQuestion%201%5D*1%2C%20or%26nbsp%3B%3DCLEAN(%40%5BQuestion%201%5D)*1%2C%20etc%20and%20work%20with%20these%20columns.%20Adding%20of%20additional%20columns%20into%20the%20forms%20table%20won't%20crash%20the%20process.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E2)%20Instead%20of%20linked%20table%20use%20Power%20Automate%20which%20is%20triggered%20by%20form%20submit%20and%20adds%20rows%20to%20the%20table%20in%20your%20file.%20In%20this%20case%20not%20linked%20with%20the%20Form.%20Transformation%20could%20be%20done%20within%20Power%20Automate%20flow.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2110823%22%20slang%3D%22en-US%22%3ERe%3A%20The%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2110823%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949996%22%20target%3D%22_blank%22%3E%40Davidm54%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3ESub%20TextToNumber()%0ADim%20LO%20As%20ListObject%0A%20%20%20%20Set%20LO%20%3D%20ActiveSheet.ListObjects(1)%0A%20%20%20%20With%20LO.ListColumns(1).Range%0A%20%20%20%20%20%20%20%20.NumberFormat%20%3D%20%22General%22%0A%20%20%20%20%20%20%20%20.Value%20%3D%20.Value%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%5B%3CEM%3EThe%20Table%20name%20or%20column%20name%20could%20be%20used%20in%20place%20of%20its%20index%3C%2FEM%3E%5D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2110761%22%20slang%3D%22en-US%22%3ERe%3A%20The%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2110761%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F949996%22%20target%3D%22_blank%22%3E%40Davidm54%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20see%20no%20reason%20why%20the%20macro%20solution%20should%20not%20work%20provided%20your%20worksheet%20is%20'Sheet1'%20and%20the%20data%20is%20brought%20into%20column%201.%26nbsp%3B%20Since%20you%20say%20you%20are%20using%20Excel%20Tables%2C%20it%20would%20be%20a%20more%20precise%20way%20of%20defining%20the%20range%20to%20edit%20to%20use%20the%20column%20of%20the%20list%20object%20as%20opposed%20to%20sheet-based%20references.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2110713%22%20slang%3D%22en-US%22%3ERe%3A%20The%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2110713%22%20slang%3D%22en-US%22%3E%3CP%3EWell%2C%20this%20is%20not%20a%20perfect%20solution%2C%20but%20if%20you%20set%20up%20a%20flow%20with%20power%20automate%2C%20the%20data%20can%20be%20dumped%20to%20a%20different%20spreadsheet%20and%20stays%20as%20numbers.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2593188%22%20slang%3D%22en-US%22%3ERe%3A%20The%20dreaded%20Apostrophe%20is%20turning%20numbers%20into%20text%20in%20a%20forms%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2593188%22%20slang%3D%22en-US%22%3EThanks%20for%20specifying%20that%20you%20use%20restrictions.%3CBR%20%2F%3EI%20always%20use%20restrictions%20and%20can't%20recall%20seeing%20this%20issue%2C%20so%20I%20thought%20that%20was%20the%20cause%20when%20a%20user%20reported%20it%20to%20me.%3CBR%20%2F%3ENow%20I%20know%20that%20isn't%20necessarily%20the%20solution.%3C%2FLINGO-BODY%3E
Contributor

I answered my first question by checking one of my other sheets, but this one is a bit trickier.

 

When people use MS Forms, even for questions I have added restrictions to so it must be a number, in the associated spreadsheet, the data is presented as text, specifically: '2, '3 etc.

 

This obviously then limits options as far as what can be done with the data, conditional formatting, calculations, etc, all go out the window.

 

https://www.youtube.com/watch?v=TsqrVAdWRfE

 

This vid has a serviceable couple of solutions if it was a one off problem, however, this is a table being updated from form responses, and I'd like to remove the apostrophes as soon as new data is in there, automatically. (Regular formatting doesn't do the job.)

 

(I could not get the VBA solution offered in below video to work, unsure if I messed up, or if the fact it's dealing with a table makes a difference.)

 

Help please!

  

9 Replies

Well, this is not a perfect solution, but if you set up a flow with power automate, the data can be dumped to a different spreadsheet and stays as numbers.  

@Davidm54 

I see no reason why the macro solution should not work provided your worksheet is 'Sheet1' and the data is brought into column 1.  Since you say you are using Excel Tables, it would be a more precise way of defining the range to edit to use the column of the list object as opposed to sheet-based references.

@Davidm54 

Sub TextToNumber()
Dim LO As ListObject
    Set LO = ActiveSheet.ListObjects(1)
    With LO.ListColumns(1).Range
        .NumberFormat = "General"
        .Value = .Value
    End With
End Sub

[The Table name or column name could be used in place of its index]

@Davidm54 

I guess the Form is linked to Excel file hosted on SharePoint site. That issue is known for years, at least two workarounds

1) If you have column [Question 1] which have number as text, you may add to the table additional column Q1 as =@[Question 1]*1, or =CLEAN(@[Question 1])*1, etc and work with these columns. Adding of additional columns into the forms table won't crash the process.

 

2) Instead of linked table use Power Automate which is triggered by form submit and adds rows to the table in your file. In this case not linked with the Form. Transformation could be done within Power Automate flow.

Thanks. I'll definitely look at using that, this won't be the last time forms is being used.
Thanks. Re 1. That's good to know re extra columns, I've added columns at the end on form response sheets, but haven't been game to add columns in the middle.

2. That is the work around I ended up with this time. It's worked well, as with numbers in place, everything flows pretty easily from that. Far less reliance on formulas for what we need now, just a couple of pivots and some sheets referencing other sheets and splicers to allow folks to glance at what they want.

@Davidm54 For reference, you can also do the following (source

 

To remove single quote in front of numbers in a cell, you can highlight the entire column where the pasted text figure will be and Clear Formatting.

 

Go to Home Tab > Editing Group> Clear > Clear Formatting

 

2020-12-23 10_05_43-.png

@Davidm54 

It's always better to split Excel workbook on 3 zones

- source/raw data

- data preparation

- reporting

 

First is filled automatically by SharePoint app triggered by Forms

- in second you may generate any table and perform any calculation to transform data

- in third could be PivotTables / Charts based on second part.

Thanks for specifying that you use restrictions.
I always use restrictions and can't recall seeing this issue, so I thought that was the cause when a user reported it to me.
Now I know that isn't necessarily the solution.