Converting numbers to text by macro

[Ian Crawford]

I recently saw this macro on another forum, where it was offered as a method to change the values of numbers that had been stored as text, to numbers that can be summed. Doesn't excel have an inbuilt function that can already do this?


Sub replnos()
'replnos Macro
' Replace 0-9 with themselves to convert numbers stored as text to actual calculable numbers - Ann's cheat
'
' Cells.Replace What:="0", Replacement:="0", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="1", Replacement:="1", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="2", Replacement:="2", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="3", Replacement:="3", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="4", Replacement:="4", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="5", Replacement:="5", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="6", Replacement:="6", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="7", Replacement:="7", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="8", Replacement:="8", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="9", Replacement:="9", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

[jdelano]

You can copy the range of numbers that are text back into itself as a value and this automatically converts them to numbers. So, say a Range("A2:A25") is numbers entered as text, using this line
Sheet1.Range("A2:A25") = Sheet1.Range("A2:A25").Value will convert them to numbers.

[Aussiebear]

Welcome back to VBAX Ian. As jdelano has suggested there is a much simpler method than the code as provided.

[Paul_Hossler]

If you don't want to use VBA, an array formula will work

The numbers are entered as '1, '2, etc to ensure that they are really Text

The Sum formula is entered as Control-Shift-Enter and that will add the braces

31411

31412