Converting Numbers Stored As Text To Numbers Via Macro

Re: Converting Numbers Stored As Text To Numbers Via Macro

Do you particularly want to do this in VBA? If not, you can put the number 1 in a cell, do Copy, then select all the cells with numbers stored as text, then do Edit | Paste Special..., and check Multiply.

Re: Converting Numbers Stored As Text To Numbers Via Macro

Quote from firefiend

needs to be a macro but thanks.

I found a resource for the answer.

It would be in the spirit of sharing knowledge to post your solution!

Re: Converting Numbers Stored As Text To Numbers Via Macro

Thanks, this might help others looking for such a solution.

Re: Converting Numbers Stored As Text To Numbers Via Macro

Quote from firefiend

But of course.

Code

Sub macro()
Range("F:F").Select 'specify the range which suits your purpose
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
End Sub

This macro will take the range and convert those pesky string numbers to numerical data.

O r you could use something lik ASAP utilities

BUt i must admit it is fun getting code to do what YOU wnat!: D

Re: Converting Numbers Stored As Text To Numbers Via Macro

Quote from firefiend;331260

I'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.

Anyone have any ideas about this?

Display More

Columns ("C").TextToColumns

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!

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

Re: Converting Numbers Stored As Text To Numbers Via Macro

Thank you for posting this code. This code works when only one column is selected. How can I run this for an entire sheet. Please advise. Thank you.

Quote from trustodium;695070

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

Display More

Re: Converting Numbers Stored As Text To Numbers Via Macro

Hi Mark_4, welcome to OzGrid.

Please note the rules you agreed to upon joining. Specifically 2a:

Quote

2c. Please dont start a new question in an existing thread. This is known as Thread hi-jacking. Start your own thread. Its Free! If you need to, you can link back to the thread that you thought might have been helpful.

[hr]*[/hr]
[h]*[/h]