In a Microsoft Excel worksheet, reverse Text String or Words Order in Excel reverses the order of characters or words in a cell or range of cells. It can be useful in various scenarios, such as when you want to analyze text data in a different order or when you need to transform the text data for presentation purposes. Excel provides several built-in functions that reverse a cell’s text string or word order. Reversing the order of text or words in Excel is a simple and powerful way to manipulate text data. Whether you need to reverse the order of words in a sentence or the order of characters in a string, Excel provides the tools you need to get the job done quickly and easily.
Note: Excel provides a range of powerful features and functions for working with text data, and the ability to reverse the order of characters or words is just one of them. Whether working with small or large amounts of text data, Excel provides the tools to get the job done effectively and efficiently.
Jump to:
Reverse text string using Kutools
Kutools for Excel provides add-ins that enhance built-in functions. It includes combining sheets, merging cells without losing data, pasting to only visible cells, counting cells by color, and performing batch operations.
Reversing the text by right-left word order:
- Foremost, you have to select the range that you want to reverse.
- Click Kutools > Text Tools > Reverse Text Order, like the image given below:
3. In the Reverse Text dialog box, select the proper option from Separator which are corresponding with the cell values.
4. You can then preview the results from the Preview Pane.
6. After selecting the Separator, you must to click the OK to apply the function.
Reversing the text by Comma Separators:
Comma – A comma (,) is a punctuation used to indicate the separation or division in a sentence, a word, phrase, or clause. It is mainly to separate the sequential elements.
- Select the cells and apply this utility by clicking Kutools > Text > Reverse Text Order.
- In the Reverse Text dialog box, choose the separator which separates the cell values that you want to reverse the words. Here you are choosing the Comma Separator.
3. Then you have to click OK or Apply; the words in the cells have been reversed at once.
Note: You must check the Skip non-text check box, to prevent cells from reversing the numbers in the selected range.
Reversing the text by Carriage return Separators:
Carriage return – Carriage return is nothing, but they are different characters used to return the print head carriage to the beginning of the line. You can click where you want to insert carriage return and press the shortcut Alt+Enter. Now you can begin a new line in the same cell.
- In the very step, you must select the text or text range to reverse the string.
- Click Kutools > Text Tools > Reverse Text Order, like the image shown in the above paragraph.
3. In the Reverse Text dialog box, choose the Separator, which separates the cell values that you want to reverse the words.
4. Here you are choosing the Carriage return Separator.
5. After selecting the Separator, you have to click OK or Apply. The words in the cells have been reversed at once.
Reversing the text by other specific character Separators:
- Select the cells and apply this utility by clicking Kutools > Text > Reverse Text Order.
- In the Reverse Text dialog box, choose the separator which separates the cell values that you want to reverse the words. Here you want $ and # specific characters so that you want to mention the specific character in the other check box.
3. Then you have to click OK or Apply. The words in the cells have been reversed at once.
Reverse text string without using Kutools
We can also reverse the string without using Kutools by using a set of pre-defined formulas to reverse the string, the reverse string formula, the Text formula, and the Transpose formula. We can also use VBScript to reverse the string without using Kutools. These ways are shown below with a clear demonstration for better understanding.
Using reversestr formula
- Foremost, you have been placing the cursor in the empty cell and then using the reverse string formula after you have to select the corresponding cell to reverse the selected string.
For example,
=reversestr(cell name) that is =reversestr(A1)
2. After applying the reverse string formula, you can see the output as shown in the below image:
Using Text formula
You can also use the Text formula to reverse the string without using Kutools.
- Let us see how to reverse the string in a single cell with the text you want.
- To reverse the string “HELLO” in cell A1, you must first type the following formula in cell B1.
=MID(($A$1,LEN($A$1)-ROW(B1)+1,1)
- It should return the last character in the given string, “O“.
- Drag the fill handle at the bottom right of cell B1 until you see “#VALUE!“.
- Now, you should reach each character of the string in “HELLO” in each cell of column B in a reversed manner.
- Clear the cells which contain “#VALUE!“.
- Finally, you got the apparent result of the reverse string of the text.
Using Transpose formula
Here you can use the Transpose formula to reverse the string.
- First, select the cell where you want to reverse the string.
- For example, I selected the cell name (E1) to reverse the string containing the text string “HELLO“.
- In cell F1, type the given formula,
=TRANSPOSE(MID(E1,LEN(E1)-ROW(INDIRECT(“1:”&LEN(E1)))+1,1))
- After that, Select the entire formula in the formula bar and press F9 on your keyboard.
- It should display each character of your string, reversed and separated by commas.
- The whole thing should be surrounded by curly brackets, which means this is an array: {“o”,” l”,” l”,” e”,” H”}. Array – An Array is a collection of items of the same data type stored at contiguous memory locations.
- You need to remove the opening and closing curly brackets.
- Then you have to type the “CONCATENATE” formula in the formula bar followed by opening parentheses after the equal to sign. Close the parentheses after “H”.
- And then press the return key.
- At last, you got the result in the F1 cell.
- This calculation got done directly and in one cell. So it is a much more efficient way of using the formula to reverse a string than the Text formula.
Note: You must be clear about this; the reversed string will not change when you change the value in cell A1. You will need to repeat this process every time there’s a change. And you have to specify the correct cell name in the formula to avoid syntax errors and to get the appropriate results.
Reverse text string using VBScript
Using VBScript, you can quickly reverse the string by using the below steps,
- Initially, you have to select the Developer Menu Ribbon and select Visual Basic.
- Your VBA window opens, then click the Insert > Module.
- Now you can type the coding or copy and paste the below coding,
Sub reverse_string_range() Dim s As Range Dim cell As Range Set s = Application.Selection i = 0 For Each cell In s cell.Offset(0, 1).Value = StrReverse(cell) i = i + 1 Next cell End Sub
- Select the Range of cells containing the text you want to reverse.
- Please make sure the column next to it is blank because this is where the macro will display the reversed strings.
- Navigate to Developer > Macros > reverse_string_range > Run.
- Now, you can see the final results of the reverse string.
Conclusion
In this article, reversing the text string or word order in Excel is a useful and powerful feature that can be used to manipulate text data in a Microsoft Excel worksheet. By using built-in functions like REVERSE, MID and TRANSPOSE, you can easily reverse the order of characters or words in a cell or range of cells without manually recreating the text. Reversing the text string or word order can be helpful in various situations, such as when you need to analyze text data in a different order or when you want to transform the text data for presentation purposes. It is a quick and efficient way to manipulate text data in Excel.
Video Tutorial
Here is the video tutorial to Reversing the text string or word order in Excel for your better understanding.
FAQ
Hajira is a skilled financial analyst (Microsoft Certifications Completed) with more than 10 years of expertise in Excel. I love using Excel to assist individuals and organisations in enhancing their financial procedures. I like hiking and exploring outdoors when she’s not crunching mathematics. Follow me on my Mastodon account.
