In excel, there are times when we want to get the text reversed completely or their order with VBA code. There can be various requirements, like extracting reverse cell content, reverse cell order & so on.
In this article, we will learn the following:
How to get reverse cell content?
In Excel there are requirement to reverse the text or numbers in cells e.g. “english” to “hsilgne”
Following is the snapshot of data before output:
Following is the snapshot of require output in column B:
To get the above output, we need to follow the below steps to launch VB editor
Function CompleteReverse(rCell As Range, Optional IsText As Boolean)
Dim i As Integer
Dim StrNewTxt As String
Dim strOld As String
strOld = Trim(rCell)
For i = 1 To Len(strOld)
StrNewTxt = Mid(strOld, i, 1) & StrNewTxt
Next i
If IsText = False Then
CompleteReverse = CLng(StrNewTxt)
Else
CompleteReverse = StrNewTxt
End If
End Function
How to get all the words in reverse order from a cell?
We will have couple of codes to find the solution. To get all words in complete reverse order, we will copy & paste the following code in module
Function ReverseOrder1(Rng As Range)
Dim Val As Variant, Counter As Integer, R() As Variant
Val = Split(Application.WorksheetFunction.Substitute(Rng.Value, " ", ""), ",")
ReDim R(LBound(Val) To UBound(Val))
For Counter = LBound(Val) To UBound(Val)
R(UBound(Val) - Counter) = Val(Counter)
Next Counter
ReverseOrder1 = Join(R, ", ")
End Function
Let’s take a look at second VBA code:
Function ReverseOrder2(Rng As Range) As String
Dim Counter As Long, R() As String, temp As String
R = Split(Replace(Rng.Value2, " ", ""), ",")
For Counter = LBound(R) To (UBound(R) - 1) \ 2
temp = R(UBound(R) - Counter)
R(UBound(R) - Counter) = R(Counter)
R(Counter) = temp
Next Counter
ReverseOrder2 = Join(R, ", ")
End Function
How to reverse column order?
In case you have a requirement to reverse the order of a column data then you should have a closer look at the below code:
Sub ReverseColumnOrder()
Dim wBase As Worksheet, wResult As Worksheet, i As Long, x As Long
Set wBase = Sheets("Sheet1")
Set wResult = Sheets("Sheet2")
Application.ScreenUpdating = False
With wBase
For i = .Range("A1").CurrentRegion.Rows.Count To 1 Step -1
x = x + 1
.Range("A1").CurrentRegion.Rows(i).Copy wResult.Range("A" & x)
Next i
End With
Application.ScreenUpdating = True
End Sub
The above code will check the data in column A in sheet1 & then reverse the order in sheet 2. Refer below image
How to get reverse numbers only from text?
Example: “excel (123) tip” is the cell content
Require output: “excel (321) tip”
In excel, there can be multiple ways to get the same output & same goes to finding solution with VBA UDF’s. For this example, we will show 5 different ways.
Copy & paste the following codes in standard module:
Function ReverseNumber1(v As Variant) As String
Dim iSt As Integer, iEnd As Integer, sNum As String, sTemp As String
iSt = InStr(v, "(")
iEnd = InStr(v, ")")
If iSt = 0 Or iEnd = 0 Then ReverseNumber1 = v: Exit Function
sNum = Mid(v, iSt + 1, iEnd - iSt - 1)
For i = Len(sNum) To 1 Step -1
sTemp = sTemp & Mid(sNum, i, 1)
Next i
ReverseNumber1 = Left(v, iSt) & sTemp & Mid(v, iEnd, 5 ^ 5)
End Function
Function ReverseNumber2(s As String) As String
Dim i&, t$, ln&
t = s: ln = InStr(s, ")") - 1
For i = InStr(s, "(") + 1 To InStr(s, ")") - 1
Mid(t, i, 1) = Mid(s, ln, 1)
ln = ln - 1
Next
ReverseNumber2 = t
End Function
Function ReverseNumber3(c00)
c01 = Split(Split(c00, ")")(0), "(")(1)
ReverseNumber3 = Replace(c00, "(" & c01 & ")", "(" & StrReverse(c01) & ")")
End Function
Function ReverseNumber4(c00)
ReverseNumber4 = Left(c00, InStr(c00, "(")) & StrReverse(Mid(Left(c00, _
InStr(c00, ")") - 1), InStr(c00, "(") + 1)) & Mid(c00, InStr(c00, ")"))
End Function
Function ReverseNumber5(s As String)
Dim m As Object
With CreateObject("VBScript.Regexp")
.Global = True
.Pattern = "(\D*)(\d*)"
For Each m In .Execute(s)
ReverseNumber5 = ReverseNumber5 & m.submatches(0) & StrReverse(m.submatches(1))
Next
End With
Set m = Nothing
End Function
We can test the other 4 codes with the following formula:
1. =ReverseNumber2(A2)
2. =ReverseNumber3(A2)
3. =ReverseNumber4(A2)
4. =ReverseNumber5(A2)
All of the above 5 macro codes will provide the same output; however; one can adopt the code they are most comfortable with.
How to reverse cell content of activecell?
In case you want a macro to run on activecell only & then reverse the content. This code will not run on cell which contains formula.
We will use the following code:
Sub Reverse_Cell_Contents()
'this macro will run only on activecell' --- Comment
If Not ActiveCell.HasFormula Then
sRaw = ActiveCell.Text
sNew = ""
For j = 1 To Len(sRaw)
sNew = Mid(sRaw, j, 1) + sNew
Next j
ActiveCell.Value = sNew
End If
End Sub
If the cursor is on cell A1, which contains “exceltip”, then the above macro will convert it into “pitlecxe”.
Conclusion: We can have as many UDFs for a single solution in Microsoft Excel. This UDF will work from 2003 version to 2013.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
