Reversing cell values

[Guest]

how do I reverse the values of a cell (eg. 1234 becomes 4321)???

 

[Andy Brown]

how do I reverse the values of a cell (eg. 1234 becomes 4321)???

Try this UDF (from John Walkenbach's site) in a plain VBA module:

Function Reverse(InString) As String
'UDF to reverse string - John W
Dim i As Integer
Dim StringLength As Integer
Reverse = ""
StringLength = Len(InString)
For i = StringLength To 1 Step -1
Reverse = Reverse & Mid(InString, i, 1)
Next i
End Function

HTH,
Andy

 

[Dave Peterson]

And if you're using xl2k or higher, you can use a builtin VBA function:

Option Explicit
Function Reverse2(InString) As String
Reverse2 = StrReverse(InString)
End Function

 

[Dave Peterson]

That's odd. Both functions kept the leading zeros for me. (xl2003)

That's when I called it from a worksheet cell:
=reverse(a1)
or
=reverse2(a1)

But if I called it from a sub and populated another cell, I lost the leading
0's.

Sub testme()
Range("B1").Value = Reverse(Range("a1"))
End Sub

But you could do this:
Sub testme2()
Range("B1").Value = "'" & Reverse(Range("a1"))
End Sub

or even:

Sub testme3()
With Range("B1")
.NumberFormat = "@"
.Value = Reverse(Range("a1"))
End With
End Sub