Reversing Excel Cell Contents
Posted
Wed, Sep 9 2009 17:43
by
Nate Oliver
This is a bit of a tricky subject, in that it depends what you mean by reversing. This particular entry will explore reversing blocks of a String, versus an exact reverse of everything.
So, here's an example, how do I get from this:
4189 > 3743 > 2040 > 949 > 195 > 5
To this:
5 > 195 > 949 > 2040 > 3743 > 4189
There's no way to do this natively, in Excel, that I can think of. At least, not without really working your tail off. So, we'll, naturally, roll our own.
We'll make this a two-step process, a UDF to accept the data, and a Sub Routine to sort it. That would appear as the following:
Public Function stringReverse(ByRef strIn As String) As String
Dim tmpArr() As String
tmpArr = Split(strIn)
Call Reverse(tmpArr)
stringReverse = Join(tmpArr, " ")
End Function
Public Sub Reverse(ByRef lParams() As String)
Dim i As Long, tmp As String
For i = 0 To UBound(lParams) / 2 - 1
tmp = lParams(i)
lParams(i) = lParams(UBound(lParams) - i)
lParams(UBound(lParams) - i) = tmp
Next i
End Sub
A couple of things to note.
1) This requires a VBA environment greater than Excel 97, as it uses String Functions that were introduced in VB6.
2) This is a fairly efficient process, you might note that the processing Sub Routine reduces the iteration against the array by 2.
There's a few different ways to implement this. The first is with VBA, e.g.,
Sub foo()
MsgBox stringReverse("4189 > 3743 > 2040 > 949 > 195 > 5")
End Sub
And you might be wondering at this point, why not use VBA's native strReverse() function? E.g.,
Sub bar()
MsgBox strReverse("4189 > 3743 > 2040 > 949 > 195 > 5")
End Sub
Well, strReverse() reverses everything a little too well. Notice what happens with 4189, for example - it becomes 9184 - not quite what we're after.
The other manner in which to use this, which is why we created a UDF as the initial call, is as a Worksheet User Defined Function call, e..g,
=stringreverse(A1)
In addition to the text examples in this blog post, I'm attaching a working example. It includes VBA and is attached with no assurances or warranties - open it at your own risk. Otherwise, have a nice evening.