Take the 2-minute tour ×

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

share|improve this question
So, are you trying to convert text in a cell? Or a string stored in a variable? The approach may be different in these cases. –  BradC Apr 16 '09 at 15:42

2 Answers 2

Use the VALUE() function.

Also, from here:

Sub ConvertToNumbers() 
  Cells.SpecialCells(xlCellTypeLastCell) _
    .Offset(1, 1).Copy
  Selection.PasteSpecial Paste:=xlPasteValues, _
  With Selection
     .VerticalAlignment = xlTop
     .WrapText = False
  End With
End Sub
share|improve this answer

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
        numConv = val(num)
    End If

End Function
share|improve this answer
He's not trying to flip the sign. The parenthesis (1234) is an indicator of a negative number. –  BradC Apr 16 '09 at 15:41
Also why to use Mid(num, 1, 1) = "(" And Mid(num, (Len(num)), 1) = ")" You should be fine with Left(num,1) = "(" And Right(num,1) = ")" –  Adarsha Apr 20 '09 at 21:02

Your Answer

