How to Reverse/Mirror Contents of Cell in Excel

This may seem like a weird topic to discuss but I've always found Excel to be highly useful in manipulating data and can often be quicker than writing one-off scripts.

One of the most used features is the Text-to-Columns button (found in the Data ribbon of Excel), which splits tab and comma delimited files into individual columns. But sometimes the values may not all have the same number of columns. For instance, let's look at the following example data:

CN=Computer1, OU=Desktop Users, OU=BuildingA, DC=TEST, DC=LOCAL
CN=Laptop2, OU=Traveling Users, OU=Finance, OU=BuildingC, DC=TEST, DC=LOCAL

Imagine the above is a two-line sample from a large set of data and you are trying to find the building. As you can see, the first row would have the building in the 3rd column whereas the second row would have building in the 4th column. However, the building is the 3rd to last column from the end - so if we reverse it, the data will be in column 3 for both rows.

  • Let's put our data in a spreadsheet in Excel
    data

  • In Excel, make sure you have the Developer tab enabled. If not, go to File -> Options -> Customize Ribbon and check the box next to Developer in the right pane.
    Enable Developer tab

  • Click the Developer tab and click the Visual Basic button

  • Right click on ThisWorkbook -> Insert -> Module
    Add module

  • Copy and paste the below code into the module window, then close the visual basic window to go back to your spreadsheet

Public Function RevStr(Rng As Range)
RevStr = StrReverse(Rng.text)
End Function

  • In Excel, you should now have a function called RevStr. In B1, type =RevStr(A1) and fill down into B2
    revstr

  • Now you should see the data mirrored. Copy and paste column B as values. This will replace the formula with the actual text so you can parse it.
    paste as values

  • Now you can delete column A. Do a Text-to-Columns on your reversed data that is now in column A. You should have something that looks like this:
    parsed data

  • Assuming you can read backwards a bit, you can see column C now contains the data containing the building. To get the data back to readable, insert a column and do another =RevStr(C1)
    data reversed again

This tutorial was probably a bit more in-depth than it needed to be but being able to reverse a string is a very handy feature for certain types of data. Hope this helps!

comments powered by Disqus