Numbers as text

[LearnNewThings]

I have a Microsoft form writing directly to an excel sheet. Unfortunately, as we all know, we can only submit "text," but use the restriction in the form to allow only numbers. This causes the values to be stored in the excel sheet as text. I have equations and pivot tables (with pivot charts) that need these values to be stored as a number. Currently, I manually convert them all to numbers, but this is not an easy process to hand over to someone else and, quite frankly, is a pain to manage myself (sometimes the responses are scattered throughout the worksheet, so there is a chance I may miss a conversion). I have two questions that I am hoping to get an answer to.
1. Is there an elegant way to build out my equations and modify my pivot table/charts that use the number stored as text without requiring me (or someone else) going into the raw data and converting it all to number?
2. Is there an equation I can create that looks at the raw data and lets me know if there are any unconverted cells? (this will help me ensure I have converted all to number, assuming there is not a clean answer to #1). I have tried counting statements, but it doesn't work as countif doesn't see a "number stored as text" as text.

 

[Joe4]

Can you use VBA?
VBA can pretty easily convert all the text values to numbers.

 

[Jeffrey Mahoney]

The most elegant way to make sure a number is a number in your formulas is to precede the equation with --

You can use this macro to convert text numbers to values. Highlight the range and run this. I use a Ribbon button linked to a macro in my PERSONAL workbook.

Sub ConvertToNumber()
  Selection.NumberFormat = "General"
  Selection.Value = Selection.Value2
End Sub

 

[LearnNewThings]

I have a Microsoft form writing directly to an excel sheet. Unfortunately, as we all know, we can only submit "text," but use the restriction in the form to allow only numbers. This causes the values to be stored in the excel sheet as text. I have equations and pivot tables (with pivot charts) that need these values to be stored as a number. Currently, I manually convert them all to numbers, but this is not an easy process to hand over to someone else and, quite frankly, is a pain to manage myself (sometimes the responses are scattered throughout the worksheet, so there is a chance I may miss a conversion). I have two questions that I am hoping to get an answer to.
1. Is there an elegant way to build out my equations and modify my pivot table/charts that use the number stored as text without requiring me (or someone else) going into the raw data and converting it all to number?
2. Is there an equation I can create that looks at the raw data and lets me know if there are any unconverted cells? (this will help me ensure I have converted all to number, assuming there is not a clean answer to #1). I have tried counting statements, but it doesn't work as countif doesn't see a "number stored as text" as text.

I don't think I can use VBA, as converting the file from an Excel workbook to a macro-enabled will break the link between the MS form and the workbook (at least that's what I understand)

 

[Jeffrey Mahoney]

Seems to me you can create a macro to evaluate the numbers and convert them in a separate workbook. You can either use PERSONAL or just create another workbook.

My client doesn't want a macro workbook for our main costing tool. I just created a macro workbook that does all the importing into that costing tool.