5 Ways to Reverse Text in Microsoft Excel

Do you need the text in your report or dashboard in the reverse direction?

A string is a set of characters. It behaves like an Array object. Arrays are one of the most used data structures in programming languages. you can simply consider a text as an array of individual characters.

You might want to reverse the order of the characters in your string. For example, the hello world text reversed would return the output as dlrow olleh.

Text is written in different ways, such as mirror writing or backward, flip text, reverse text, etc. The text AMBULANCE is often written as ECNALUBMA in front of an ambulance so when other drivers look into their rear-view mirrors, they will spot an ambulance trailing behind them.

This blog post cover covers advanced methods to reverse your text in Excel such as using the functions CONCAT and TEXTJOIN, using Power Query to help with multiple data files or large data, or VBA and Office Scripts methods to reverse the text in Excel for your report and automation projects.

Get your copy of the example workbook used in this post to follow along!

Reverse Text in Excel with CONCAT function

Suppose you have the reversed text data in Column A and need it to reverse in Column B for readability and further analysis. 

Excel does not have a specific function to reverse the strings. You have to use a combination of 4 functions CONCAT, MID, SEQUENCE, and LEN to reverse the string in Excel.

LEN( text )

The LEN function returns the number of characters in the text.

SEQUENCE( rows, [columns], [start], [step] )

The SEQUENCE function generates a list of sequential numbers in an array.

MID( text, start_num, num_chars)

The MID function returns a specific number of characters from a text.

CONCAT( txt1, [txt2], ...)

And finally, the CONCAT function can be used to join two or more text strings together.

= CONCAT( MID( A2, SEQUENCE( LEN( A2 ), , LEN( A2 ), -1 ), 1 ) )

Type the formula into an empty cell and then press the Enter key. Drag the fill handle down the range to copy the formula. In this example, the text to reverse is in cell A2.

In the above formula, the LEN function counts the number of characters in cell A2 and returns 6.

The SEQUENCE function generates an array {6;5;4;3;2;1}. The MID function returns the specific character from each position of the array from the SEQUENCE function. Finally, CONCAT joins all the characters returned by the MID function.

Each function and the respective output for the text in cell A2.

FunctionOutput
LEN(A2)6
SEQUENCE( ... ){6;5;4;3;2;1}
MID( A2, ..., 1 ){a;m;a;z;o;n}
CONCAT( ... )amazon

This formula results in the reversal of any text string.

Reverse Text in Excel with TEXTJOIN function

This method is similar to the above CONCAT method, except it’s going to use the TEXTJOIN function instead of CONCAT function.

The TEXTJOIN function allows you to combine various cell texts into a single cell and separate them with a delimiter. In this case, an empty string will be used as the delimiter.

=TEXTJOIN( "", 1, MID( A2, SEQUENCE( LEN( A2 ), , LEN( A2 ), -1 ), 1 ) )

Add the above formula in any empty cell, and then press the Enter key. You can copy down the formula as needed with the fill handle by clicking and dragging it down.

The TEXTJOIN function acts to concatenate all the characters with an empty delimiter.

Reverse Text in Excel with Power Query

Excel Power Query is an ETL tool in Excel for extracting, transforming, and loading data. It allows you to connect to and import data from databases, text files, and other spreadsheets. It also helps you to format, clean, modify, and process the connected data for reporting and analysis. 

You can use the power query GUI editor to process and format data very easily. In this section, you learn how to use Text.Reverse function in power query to reverse the text in Excel.

Follow these steps to use the Power Query Text.Reverse function to reverse you text.

  1. Select the range of cells with the text to reverse. In this example, the text is in A1:A6 of the Data Sheet.
  2. Choose the option From Table/Range option in the Data Stab. This will open the Power Query Editor window
  1. You can rename the query to something such as POWERQUERY.
  2. Select the command Custom Column in the Add Column tab to open the Custom Column dialog box.
  1. Type the name Reverse/Flip in the New column name box. This creates a new column heading name.
  2. Type the formula =Text.Reverse( [Text] ) in the Custom column formula input box.
  3. Press the OK button.
  1. Select the command Close & Load and Press Close & Load To… to open the Import Data dialog box.
  1. In the Import Data dialog box, select the Table option, and click the option New worksheet to insert the transformed Power Query table into a new worksheet.

This will load the data into your Excel workbook along with your new column containing the reversed text.

The best part is when you add new data, you can go to the Data tab and press the Refresh command to rerun the query and return the new reversed text.

Reverse Text in Excel with VBA

Visual Basic for Applications is a programming language available in Excel. You can use it to automate repetitive tasks in Excel.

StrReverse( expression )

VBA even has a dedicated function to reverse the order of characters in your text strings. The StrReverse function in VBA will take any text string and return the reversed text.

You can use a loop to iterate over a range of cells and use StrReverse function to reverse the text.

Sub ReverseText()

'Declare variable
Dim rge As Range
'Assign range of cells
Set rge = Sheets("VBA").Range("A2:A6")

'loop through each cell in the range A2:A6
For Each rg In rge
    'StrReverse function reverses the cell text and stores it in the adjacent cell
    rg.Offset(0, 1).Value = StrReverse(rg.Value
Next

End Sub

Add the above VBA code to your module in the visual basic editor. Press Alt + F11 on your keyboard to open the VBA editor and then go to the Insert menu and select the Module option to create a new module.

The VBA macro first declares the variable rge, and then assigns the range of cells A2:A6 to the variable. This is the range where the text to reverse is stored.

The For Each rg In rge syntax loops through the range of cells in A2:A6 and in each iteration, the script reverses the text using StrReverse and stores it in an adjacent cell.

To Run the ReverseText macro, press the keyboard shortcut key Alt + F8 to open the Macro dialog box, Select the ReverseText Macro in the Macro list, and then press the Run button.

Reverse Text in Excel with Office Scripts

Office Scripts works in both the web and desktop versions of Excel and is another method that allows you to automate difficult tasks.

This can be used to reverse text as well. The code will first split the text as an individual character array, then reorder the array, and then Join it together.

Go to the Automate tab and select the New Script option to open the Office Script editor and create your new script.

function main(workbook: ExcelScript.Workbook) {

    // Assign the worksheet, data range and number of rows in the range to the respective variables
    let wSheet = workbook.getWorksheet("OfficeScripts");
    let rg = wSheet.getRange("A2:A6");
    let rCnt = rg.getRowCount();

    // Loop through the range of cells A2:A6
    for (let i = 0; i < rCnt; i++) {

        // Get current cell value and store it in the variable celValue as a number
        let celValue = rg.getCell(i, 0).getValue() as string

        // Split the celValue as an individual character and assign it in an array
        let splitText = celValue.split("");

        // Reverse the order of the characters in the array
        let charArr = splitText.reverse();

        // Join and store the text in an adjacent cell
        rg.getCell(i, 1).setValue(charArr.join(""));
    }
}

Add the above script to the Code Editor and then press the Save button.

The above script assigns the worksheet OfficeScripts, the range of cells A2:A6, and the number of rows in the range to the respective variables. You used the for loop to loop through the range of cells A2:A6.

In each iteration, the script assigns the current cell value to the variable celValue. Then the split function is used to split the text as an individual character.

The reverse function reverses the array of items. Finally, the join function joins the array and writes it in an adjacent cell.

To run the Reverse Text Office scripts, select your script Reverse Text from the Office Scripts dropdown items in the Automate menu and press the Run button in the Code Editor window.

Conclusions

Reversed text can be useful in a variety of situations for a more artful appearance or to draw attention to a particular phrase within a paragraph.

Unfortunately, there is no built-in tool to reverse your text, but there are several great methods to get the job done.

You can use a combination of Excel functions with CONCAT or TEXTJOIN to reverse text. The Power Query method works well if you are working with multiple data sources or large data files, and VBA or Office Scripts code can be used in your automation projects to reverse text.

If you have used any other method or tricks to reverse text, let me know in the comments section below.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

1 Comment

  1. Meni Porat

    Here’s another method (CONCAT & SEQUENCE):
    =CONCAT(LEFT(RIGHT(A1,SEQUENCE(LEN(A1)))))

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃