Converting the Text into Number

Question

Morning All,

Today i came up with the new assignment of Converting the Text into number automatically by VBA Codes. For Example '1233 need to converted to 1233 and (1234) need to converted to -1234 without any human interference. Some Advice is very helpful.

Regards, A Jabeer Ali

Answer 1

Misunderstood your question, my apologies.

This worked for me, even outside excel:

Function numConv(num As String) As Double

    If Mid(num, 1, 1) = "(" And Mid(num, (Len(num)), 1) = ")" Then
        num = Replace(num, "(", "")
        num = Replace(num, ")", "")
        numConv = val(num) * -1
    Else
        numConv = val(num)
    End If

End Function

Answer 2

Use the VALUE() function.

Also, from here:

Sub ConvertToNumbers() 
  Cells.SpecialCells(xlCellTypeLastCell) _
    .Offset(1, 1).Copy
  Selection.PasteSpecial Paste:=xlPasteValues, _
     Operation:=xlPasteSpecialOperationAdd
  With Selection
     .VerticalAlignment = xlTop
     .WrapText = False
  End With
  Selection.EntireColumn.AutoFit
End Sub