VBA code to convert Text to Number

Anonymous
2012-03-27T02:37:30+00:00

HI,

Do everyone knows what is the VBA code to convert Text to Number?  like '01116 convert to 1116.

Best Regards,

Lawrence

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2012-03-27T13:58:48+00:00

Hi,

try this..

convert  text number to number

Sub ConvertTextNumberToNumber()

For Each WS In Sheets

On Error Resume Next

For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)

If IsNumeric(r) Then r.Value = Val(r.Value)

Next

Next

End Sub

Was this answer helpful?

60+ people found this answer helpful.
0 comments
Answer accepted by question author
HansV 462.7K Reputation points MVP Volunteer Moderator
2012-03-28T07:47:45+00:00

Some of the cells had a value prefixed with ' to make them text, and they had also been formatted as Text. The solution, as already provided by others, is to format the cells as General.

Was this answer helpful?

30+ people found this answer helpful.
0 comments

31 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.7K Reputation points MVP Volunteer Moderator
    2012-03-27T10:05:13+00:00

    Can you provide more detailed information?

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2012-03-27T03:30:26+00:00

    Well you want to use it just to convert a Cell anywhere in your Workbook, no need to build a function for this in VBA.

    You can use the build-in function called VALUE.

    So for example when C2 is the Text field, then in D2 you can Type:

    =VALUE(C2)

    It will convert the Text value to Number value.

    Hope this helps,

    Daniel

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2012-03-27T03:04:04+00:00

    Hi Lawrence,

    You can use the Val() function for this.

    If for example C2 is Text '01116 and you want D2 return the a Number :

    Sub test()

    Range("D2").Value = Val(Range("C2").Value)

    End Sub

    Hope this helps,

    Daniel

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments