Converting a column of text to number with macro

[JohnSeito]

Hi

Could someone show me a code that would convert a column of text to number with macro.

Thanks appreciate it.

 

[jproffer]

Assuming you mean numbers that are formatted as text:

Sub Make_Number()
On Error Resume Next
Application.ScreenUpdating = False
   Dim Cell As Range
      Selection.NumberFormat = "General"
      For Each Cell In Selection
         Cell.Value = Cell.Value * 1
      Next Cell
Application.ScreenUpdating = True
End Sub

You'll have to highlight the cells you wish to do this on.

 

[jim may]

Highlight the Cells that you wish to convert and run this Macro

Sub Foo()
Set Rng = Selection
For Each c In Rng
With Rng
c.Value = c.Value + 0
End With
Next c
End Sub

 

[JohnSeito]

Ok thank you. I will give it a try and let you know if I have any questions.

 

[JohnSeito]

Hi,

I looked at both your solutions and it works and looks good. But both your solution works on number as text in a column ONLY. In my case my column doesn't only have numbers but also text. So how would I convert it to number for this column when like this?

example of how my datas may look:

V2415
E0093
1213 <b> number as text </b>
123800117m
123800021m
12131 <b> number as text </b>
123800020m

so it seems the solution is to make the column into all text or make the column into all numbers even if it is already a number or already a text. Could you provide me a code that would convert it to either text or number even if it's already text or number for some of the datas. This is b/c I want to have two sets of datas that are same, if column A is number for column a row 3, then column b row 5 for the same that should also be number.

the code provided on top right now gives error b/c the text can't be times by 1 or plus by 0.

Thanks.

 

[jim may]

Here's a User Defined Function (UDF) you can paste into a Standard Module of your wb.
Then using a blank or now (helper) column, say in Cell B1 -- where A1 has your data

=ExtractNums(A1)

copy down


Public Function ExtractNums(c) As String
Dim i As Integer
Dim MyNums As String

MyNums = ""
For i = 1 To Len(c)
If InStr(1, "0123456789", Mid(c, i, 1), vbTextCompare) > 0 Then
MyNums = MyNums + Mid(c, i, 1)
End If
Next i
ExtractNums = MyNums

End Function

 

[jindon]

Select the Range and try

Sub test()
Selection.Value = Selection.Value
End Sub

 

[JohnSeito]

I see what the function is doing. But I don't want it to get rid of the letters. I just want to keep it like that but change the text that are as number to either number or text. The reason why is because I am comparing column A to column B, kind of like a vlook up to search data.
But b/c of formatting issue with the datas, on either side of column A or B the number maybe be either text or number, one side can be number and one side can be text for the same data.

I just want to make both side the same, without getting rid of the letter like the function did.


I think maybe if we could change the number that may possibly be number to text would work, but the code have to not give error when if it encounter text, or numbers.

Hope you know what I mean.

Thanks

 

[theozz]

Hi ! Here Suggest to you

<table style="border-collapse: separate;" colspan="2" border="7" cellspacing="0"><tbody bgcolor="#ffffff"><tr height="36"><td colspan="2"> Nice Question Thanks.
DataSheet= Sheet1</td></tr><tr><td align="right" width="30"><table style="table-layout: fixed;" rowspan="8" cellspacing="1" width="30"><tbody bgcolor="#ffffff"><tr height="18"><td>
</td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">1 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">2 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">3 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">4 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">5 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">6 </td></tr><tr height="18"><td align="right" bgcolor="#dcdcdc">7 </td></tr></tbody></table></td><td width="369"><table colspan="3" rowspan="3" style="table-layout: fixed;" bgcolor="#939393" border="0" cellspacing="1" width="369"><tbody bgcolor="#ffffff"><tr height="18"><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="128">A</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="128">B</td><td style="font-family: verdana;" align="center" bgcolor="#dcdcdc" width="112">C</td></tr><tr height="18"><td align="left" bgcolor="#ffffff" width="128">V2415</td><td align="left" bgcolor="#ccffcc" width="128">V2415</td><td align="right" bgcolor="#ccffcc" width="112">2415</td></tr><tr height="18"><td align="left" bgcolor="#ffffff" width="128">E0093</td><td align="left" bgcolor="#ccffcc" width="128">E0093</td><td align="right" bgcolor="#ccffcc" width="112">93</td></tr><tr height="18"><td align="left" bgcolor="#ffffff" width="128">1213</td><td align="right" bgcolor="#ccffcc" width="128">1213</td><td align="right" bgcolor="#ccffcc" width="112">1213</td></tr></tbody></table><table colspan="3" rowspan="4" style="table-layout: fixed;" bgcolor="#939393" border="0" cellspacing="1" width="369"><tbody bgcolor="#ffffff"><tr height="18"><td align="left" bgcolor="#ffffff" width="128">123800117m</td><td align="left" bgcolor="#ccffcc" width="128">123800117m</td><td align="right" bgcolor="#ccffcc" width="112">123800117</td></tr><tr height="18"><td align="left" bgcolor="#ffffff" width="128">123800021m</td><td align="left" bgcolor="#ccffcc" width="128">123800021m</td><td align="right" bgcolor="#ccffcc" width="112">123800021</td></tr><tr height="18"><td align="right" bgcolor="#ffffff" width="128">12131</td><td align="right" bgcolor="#ccffcc" width="128">12131</td><td align="right" bgcolor="#ccffcc" width="112">12131</td></tr><tr height="18"><td align="left" bgcolor="#ffffff" width="128">123800020m</td><td align="left" bgcolor="#ccffcc" width="128">123800020m</td><td align="right" bgcolor="#ccffcc" width="112">123800020</td></tr></tbody></table></td></tr></tbody></table>
<colspan=5 width="800" rowspan="5" height="90"></colspan=5><table style="border-collapse: separate;" border="6" cellspacing="0"><tbody><tr height="24"><td colspan="5" align="center">Used Formula ...(With Running MicrosoftExcel Ver 2003)</td></tr><tr height="24"><td align="center" bgcolor="#d3d3d3" width="4%">No</td><td align="center" bgcolor="#d3d3d3" width="9%">Addr'</td><td align="center" bgcolor="#d3d3d3" width="65%"> If use below Formula, You'll Get Result as Right</td><td align="center" bgcolor="#d3d3d3" width="15%">Result</td><td align="center" bgcolor="#d3d3d3">Formula's</td></tr><tr height="20"><td align="center">1</td><td align="center" bgcolor="#ccffcc">B1</td><td bgcolor="#ccffcc">=V(A1)</td><td align="left">V2415</td><td align="right"><form name="PrNames1"><input onclick='window.clipboardData.setData("Text","=V(A1)");' value="Do Copy" name="MyNames1" type="button"></form></td></tr><tr height="20"><td align="center">2</td><td align="center" bgcolor=""> </td><td>B1 His Formula Used This Cell -> B1:B7</td><td align="left"> </td><td> </td></tr><tr height="20"><td align="center">3</td><td align="center" bgcolor="#ccffcc">C1</td><td bgcolor="#ccffcc">=w(A1)</td><td align="right">2415</td><td align="right"><form name="PrNames3"><input onclick='window.clipboardData.setData("Text","=w(A1)");' value="Do Copy" name="MyNames3" type="button"></form></td></tr><tr height="20"><td align="center">4</td><td align="center" bgcolor=""> </td><td>C1 His Formula Used This Cell -> C1:C7</td><td align="left"> </td><td> </td></tr><tr height="24"><td colspan="5">
Formula Has User Defind Function. Careful !!!
How about this suggest?
</td></tr></tbody></table>

Option Explicit

Function V(var As Variant)' === Module Start
On Error Resume Next
V = CDbl(var)
If Err.Number = 13 Then
V = var
End If
End Function ' ___ Module Stop


Function W(var As Variant)' === Module Start
On Error Resume Next
W = CDbl(var)
If Err.Number = 13 Then
Dim i As Double
Dim cnt As Double
cnt = Len(CStr(var))
For i = 1 To cnt
If IsNumeric(Mid(var, i, 1)) Then
W = W & Mid(var, i, 1)
End If
Next
W = CDbl(W)
End If
End Function ' ___ Module Stop

 

[JohnSeito]

<code>Sub test()
Selection.Value = Selection.Value
End Sub</code>

This could work, but I have to do this

Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select

first. its there a macro loop that will run the column until the end?