How to reverse text string or words order in Excel?

When you use the Excel worksheet, how do you reverse the text string or words order in Excel? For example, you want to reverse “Excel is a useful tool for us” to “su rof loot lufesu a si lecxE”. Or sometimes you may reverse the words order such as “Excel, Word, PowerPoint, OneNote” to “OneNote, PowerPoint, Word, Excel”. Normally this is somewhat difficult to solve this problem. Please look at the following methods:

Reverse text string with formula in Excel

Reverse text string with VBA

Reverse words separated by interval symbob with VBA

A handy way to reverse text string or words order with Kutools for Excel

Kutools for Excel: add 120 new features in Excel. Save one hour every day.
Classic Menu for Office: brings back classic menus to Office 2010 and 2013 (includes Office 365).
Office Tab: brings tabbed interface to Office as the use of web browser Chrome, Firefox and Internet Explorer.

arrow blue right bubble Reverse text string with formula in Excel

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

In Excel, you can use a formula to reverse text string. For example, there are some text string as shown as below in Excel:

Step 1. Type this formula =IF(LEN(A1)<1,"",MID(A1,LEN(A1),1))&IF(LEN(A1)<2,"",MID(A1,LEN(A1)-1,1))&IF(LEN(A1)<3,"",MID(A1,LEN(A1)-2,1))&IF(LEN(A1)<4,"",MID(A1,LEN(A1)-3,1))&IF(LEN(A1)<5,"",MID(A1,LEN(A1)-4,1))into B1, and then press Enter button. See screenshot:

Step 2. Copy this formula to other cells by dragging the bottom right corner of the Cell B1. See screenshot:

Note:

1. In this case, each of text string has five characters, so here the formula has five sections. If the text string has six characters, you need to add &IF(LEN(A1)<6,"",MID(A1,LEN(A1)-5,1)) to the end of the formula.

2. If each text string has a different number of characters in the column, you cannot drag the bottom right corner to copy the formula, you need to type the different formulas one by one.

arrow blue right bubble Reverse text string with VBA

Hot
Amazing! Using Tabs in Excel like Firefox, Chrome, Internet Explore 10!

Supposing you have a range of text strings which you want to reverse, such as “add leading zeros in Excel” to “lecxE ni sorez gnidael dda”. You can reverse the text with following steps:

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Modulewindow.

Sub ReverseText()
'Updateby20131128
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
xValue = Rng.Value
xLen = VBA.Len(xValue)
xOut = ""
For i = 1 To xLen
getChar = VBA.Right(xValue, 1)
xValue = VBA.Left(xValue, xLen - i)
xOut = xOut & getChar
Next
Rng.Value = xOut
Next
End Sub

3. Then press F5, a dialog is displayed on the screen, and you need select a range to work with. See screenshot:

4. And then press OK, and all the text strings have been reversed. See screenshot:


arrow blue right bubble Reverse words separated by interval symbol with VBA

If you have a list of cell words which are separated by commas as this “teacher, doctor, student, worker, driver”, and you want to reverse the words order like this “drive, worker, student, doctor, teacher”. You can also use follow VBA to solve it.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following macro in the Module window.

Sub ReverseWord()
'Updateby20131128
Dim Rng As Range
Dim WorkRng As Range
Dim Sigh As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Sigh = Application.InputBox("Symbol interval", xTitleId, ",", Type:=2)
For Each Rng In WorkRng
strList = VBA.Split(Rng.Value, Sigh)
xOut = ""
For i = UBound(strList) To 0 Step -1
xOut = xOut & strList(i) & Sigh
Next
Rng.Value = xOut
Next
End Sub

3. Then press F5, a dialog is displayed on the screen, and you need select a range to work with. See screenshot:

4. And then press Ok, another dialog is popped out for you to specify the interval symbol. See screenshot:

5. Then clcik OK, and you can see the words selected are reverse, see screenshot:


arrow blue right bubble A handy way to reverse text string or words order with Kutools for Excel

The Kutools for Excel’s Reverse Text Order can help you quickly and conveniently to reverse various text strings. It can do following operations:

Reverse the text from right to left, such as “tap some words” to “sdrow emos pat”;

Reverse the text are separated by space, such as “apple orange grape” to “grape orange apple”;

Reverse the text are separated by semicolon, comma, carriage return or other specific characters.

Kutools for Excel includes more than 120 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

When you have installed Kutools for Excel, you can do as this:

1. Select the range that you want to reverse.

2. Click Kutools > Text Tools > Reverse Text Order, see screenshot:

3. In the Reverse Text dialog box, select the proper option from Separator which are corresponding with the cell values. And you can preview the results from the Preview Pane. See screenshot:

4. Then click OK or Apply. All the cell contents have been revered by space in this example. See screenshots:

Note:Checking Skip non-text cells to prevent you reversing the numbers in selected range.

To know more about this function, please visit Reverse Text Order.


Related article:

How to flip the first and last name in cells in Excel?


Kutools for Excel

More than 120 Advanced Functions for Excel 2013, 2010, 2007 and Office 365.

screen shot

btn read more     btn download     btn purchase

 

Comments  

+9#Vineet2014-02-04 06:07
This is absurd. So much code to perform just a simple task of reversing the string? :o
Just 3 line function needs to be added in the module as below:

Function strrev(strValue As String)
strrev = StrReverse(strValue)
End Function

Now the formula =strrev(A1) can be used in Excel sheet. This works since StrReverse is an inbuilt function of VBA. :D
Reply | Reply with quote | Quote
-2#Faseeh2014-05-28 11:52
Another one...

Function InvertText(str As String)
'By Faseeh Muhammad
Dim m As Integer
For m = Len(str) To 1 Step -1
countRepp = countRepp & Mid(str, m, 1)
Next m
End Function
Reply | Reply with quote | Quote
-2#Faseeh2014-05-28 11:56
Sorry a little editing

Function InvertText(str As String)
'By Faseeh Muhammad
Dim curr As String
Dim m As Integer
For m = Len(str) To 1 Step -1
countRepp = countRepp & Mid(str, m, 1)
Next m
Inverttext
End Function
Reply | Reply with quote | Quote

Add comment


Security code
Refresh