Forum Discussion
The dreaded Apostrophe is turning numbers into text in a forms spreadsheet
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.
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.