Posts by trustodium

trustodium · Dec 13th 2013

Re: Converting Numbers Stored As Text To Numbers Via Macro

As this comes up very high in Google search result so I thought to reply to this very old post.
The best and quick way I find is to use text to column.

NOTE: you need to select your range before running this code and it works only on one column at a time.
You can define your range within your sub if it is known, eg range(range("a1").range("a1").end(xldown)).select
It is lightning quick though!

Code

Sub Txt_to_No()
     On Error GoTo errhndlr
     
      Selection.TextToColumns Destination:=Selection, DataType:=xlDelimited, _
             TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
             Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
             :=Array(1, 1), TrailingMinusNumbers:=True
      Exit Sub
 errhndlr:
     MsgBox "something something vbexcclamation, "something"
 End Sub

Display More