Convert extracted number - Paste Special / Add

[andibevan]

Hi All,

I am trying to automate part of a manual process which I regularly hav
to do.

I extract some data from a tool but when it is inputted into excel
some of the columns are stored as text not a number (i.e. format).
This prevents me performing calculations and I am unable to resolve i
by simply changing the format.

I therefore have to copy the relevant column and then "Paste Special
Add" to a blank column so that they are recognised as numbers.

Is there a simple way of converting numbers from the "text format" t
"numerical" format.

i.e.

Cell A1 = 1 --------------------> Cell A1 =1
Format = text Format = number

Thanks

And

 

[Harald Staff]

Hi Andy

Select the cells in question and run this little macro:

Sub MakeNumbers()
Dim Cel As Range
For Each Cel In Selection
If Cel.HasFormula = False Then
If IsNumeric(Cel.Value) Then
Cel.Value = Cel.Value * 1
End If
End If
Next
End Sub

HTH. Best wishes Harald