Share via

Microsoft forms automatically storing numbers as text, I have to manually change the specific column in excel to number format after each new entry.

Anonymous
2023-06-27T18:42:54+00:00

Hello!

I have been running into this issue and can't seem to get past it. I have a Microsoft forms survey linked to an excel sheet. One of the questions asks about a certain expense amount. All of the data is automatically uploaded into excel; however, the numbers are stored as text. This is an issue because I have a pivot table that links to these numbers to create a final expense total for the year, however, it doesn't update unless I highlight the column after each new entry and change it to number format either by going to the data tab and using the Text to columns option, or just changing the whole column to a number format. Doing this works temporarily, but I am anticipating getting a few forms submissions each day, and it doesn't make sense for me to have to change each entry to a number format in order to have the expense table work.

I was wondering if there is a way to have the data from forms automatically upload into excel in number format, so I don't have to manually change the column after each new entry.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

13 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-01-08T15:25:30+00:00

    Thanks for the reply.

    I need the adjustment to happen dynamically so doing text to columns wont work (unless I do it as a macro which I am trying to avoid) for me since users are continually filling out the MS Form and we are using Excel to pull real-time data.

    I also tried formatting the cells in Excel and that didn't work. The CLEAN function seemed to be the best work around.

    It just doesnt make sense if the MS Forms field is restricted to be a number only, that Excel converts it to text. Seems so odd for integrated applications.

    3 people found this answer helpful.
    0 comments
  2. Anonymous
    2024-01-31T21:36:29+00:00

    Wow, this works great. Obviously would rather not have to do that, but it works so easily with just a few clicks. Thank you!!!!

    After the data is uploaded to Excel, select a single empty cell and choose Copy. Select all the text data you want converted to numbers and press Ctrl+Alt+V, choose Add and click OK.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2023-11-01T00:59:00+00:00

    wondering if there is a way to have the data from forms automatically upload into excel in number format,

    I am afraid this number format function not available now.

    You need to convert text to number yourself.

    Or try google sheet or other similar online form.

    2 people found this answer helpful.
    0 comments
  4. Anonymous
    2024-01-18T21:13:22+00:00

    Once you save MS Forms to a Teams Group, on the Results tab of Forms there will be a linked O365 Excel button. I insert the formula in that spreadsheet to clean the numbers that come in as text.

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2024-01-05T21:52:53+00:00

    Thank you, this works great for me.

    0 comments