Convert All Numbers Stored as Text

[turtlepokerman]

Hello World,

I have a table of data that looks similar to this:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Item1[/TD]
[TD="align: center"]Apple[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Item1Weight[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Item1Height[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Item2Length[/TD]
[TD="align: center"]10[/TD]
[/TR]
</tbody>[/TABLE]

This structure repeats about 500x. I am importing data currently and the green arrows apprear in the corners and say numbers stored as text for rows B2:B4. How do I select all of the data in column B and have it automatically convert all of the numbers stored as text into numbers while leaving the text items alone? I am willing to work in VBA, since that I already have some functions created in it.

All help is appreciated!!!

 

[Rick Rothstein]

Formula bar show: 1/0

Cell Format Says:
Category: Custom
Type: General

Okay, give this modification of the code I posted earlier a try...

Sub ConvertTextNumbersToRealNumbers()
  Dim Addr As String
  Addr = "B1:B" & Cells(Rows.Count, "B").End(xlUp).Row
  Range(Addr).NumberFormat = "@"
  Range(Addr) = Evaluate(Replace("If(ISNUMBER(0+SUBSTITUTE(@,""/"",""X"")),0+@,@)", "@", Addr))
  Range(Addr).NumberFormat = "General"
End Sub

 

[turtlepokerman]

Grummet,

You method will not work, because I the number format converts the cells that say 1/0 to the date Jan 1.

I think Rick was on the correct path. This will definitely need to be handled by VBA and some conditional statements.

 

[turtlepokerman]

Rick you are my favorite person today. That worked perfectly!

Thank you for all of your help.