VBA Excel - Does cell have number formatted as text?

[Magnet Doctor]

I am working on a macro that loops through a bunch of cells in an Excel 2007 worksheet. It would be most convenient if I could determine for each cell what is in the cell, where the choices are 1) a number formatted as text, 2) a number, 3) a blank, 4) text. The flow in the loop goes differently depending on the answer. Identifying numbers and text is easy, but I'm having trouble figuring out how to tell whether the text is actually a formatted number. Can I set up some error handling, then try to convert the text to a number? Is there a better way?

 

[PA HS Teacher]

Does this User Defined Function meet your needs?

<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> MyType(R <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  <SPAN style="color:#00007F">If</SPAN> Len(R.Value) = 0 <SPAN style="color:#00007F">Then</SPAN><br>     MyType = "Blank"<br>     <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>  <SPAN style="color:#00007F">If</SPAN> R.NumberFormat = "@" <SPAN style="color:#00007F">Then</SPAN><br>     <SPAN style="color:#00007F">If</SPAN> IsNumeric(R.Value) <SPAN style="color:#00007F">Then</SPAN> MyType = "Number Formatted as Text"<br>     <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsNumeric(R.Value) <SPAN style="color:#00007F">Then</SPAN> MyType = "Text"<br>     <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN><br>  <SPAN style="color:#00007F">Else</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> IsNumeric(R.Value) <SPAN style="color:#00007F">Then</SPAN> MyType = "Number"<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> IsNumeric(R.Value) <SPAN style="color:#00007F">Then</SPAN> MyType = "Text"<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[tusharm]

Check if the cell's NumberFormat = "@" and isnumeric(cell.value)

 

[Magnet Doctor]

Thank you both. I missed the NumberFormat property in my reading.

 

[Magnet Doctor]

Thanks, again. It turns out that for my purposes I really have no need for the NumberFormat property. The combination of IsNumeric() and IsText() give me the information I really need. IsNumeric() tells me whether or not whatever is in the cell looks like number - either a number or a string that can be converted to a number.