convert to number
 

I have a coulmn of numbers that need converted to numbers. The format cell
won't work... You highlight all the numbers and the little ! box comes on and
you click convert to number. However, it does not record that function in a
macro.. I need to do this as it is part of a vlookup macro...
Thanks,
DJ

convert to number
 

Copy an empty cell.
select your column
edit|paste special|check Add

or

Select your column
data|text to columns|finish.

Duncan_J wrote:

I have a coulmn of numbers that need converted to numbers. The format cell
won't work... You highlight all the numbers and the little ! box comes on and
you click convert to number. However, it does not record that function in a
macro.. I need to do this as it is part of a vlookup macro...
Thanks,
DJ

--

Dave Peterson

convert to number
 

Here is some code that I use. It converts the selected numbers to text. You
can highlight entire columns or just a few cells. It leaves formulas alone...

HTH

Public Sub Convert()
Dim rngCurrent As Range
Dim rngToSearch As Range

Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If Not rngToSearch Is Nothing Then
Application.Calculation = xlCalculationManual
For Each rngCurrent In rngToSearch
If Left(rngCurrent.Value, 1) < "=" Then
rngCurrent.NumberFormat = "@"
rngCurrent.Value = Trim(CStr(rngCurrent.Value))
End If
Next
Application.Calculation = xlCalculationAutomatic
End If
End Sub

"Duncan_J" wrote:

I have a coulmn of numbers that need converted to numbers. The format cell
won't work... You highlight all the numbers and the little ! box comes on and
you click convert to number. However, it does not record that function in a
macro.. I need to do this as it is part of a vlookup macro...
Thanks,
DJ

convert to number
 

Sorry that is my convert to text. I usually use that one when I need
vlookups. To make this convert to number change

rngCurrent.NumberFormat = "@"
rngCurrent.Value = Trim(CStr(rngCurrent.Value))

to

if isnumeric (rngCurrent.Value ) then
rngCurrent.NumberFormat = "#"
rngCurrent.Value = Cdbl(rngCurrent.Value)
end if

HTH

"Jim Thomlinson" wrote:

Here is some code that I use. It converts the selected numbers to text. You
can highlight entire columns or just a few cells. It leaves formulas alone...

HTH

Public Sub Convert()
Dim rngCurrent As Range
Dim rngToSearch As Range

Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If Not rngToSearch Is Nothing Then
Application.Calculation = xlCalculationManual
For Each rngCurrent In rngToSearch
If Left(rngCurrent.Value, 1) < "=" Then
rngCurrent.NumberFormat = "@"
rngCurrent.Value = Trim(CStr(rngCurrent.Value))
End If
Next
Application.Calculation = xlCalculationAutomatic
End If
End Sub

"Duncan_J" wrote:

I have a coulmn of numbers that need converted to numbers. The format cell
won't work... You highlight all the numbers and the little ! box comes on and
you click convert to number. However, it does not record that function in a
macro.. I need to do this as it is part of a vlookup macro...
Thanks,
DJ

convert to number
 

Thanks for the help!
Both worked...
DJ

"Dave Peterson" wrote:

Copy an empty cell.
select your column
edit|paste special|check Add

or

Select your column
data|text to columns|finish.

Duncan_J wrote:

I have a coulmn of numbers that need converted to numbers. The format cell
won't work... You highlight all the numbers and the little ! box comes on and
you click convert to number. However, it does not record that function in a
macro.. I need to do this as it is part of a vlookup macro...
Thanks,
DJ

--

Dave Peterson