# Need a formula to reverse text

Posted by Lars on January 09, 2002 2:12 PM

This is what I have in a column:

LAX/MIA
MIA/CDG/OTP
MEX/FRA/MUC/TRS/CDG

SOME HAVE 2 CITIES SOME HAVE 5 AND IN BETWEEN
HOW DO I REVERSE THESE IN ANOTHER COLUMN?
MIA/LAX
OTP/CDG/MIA
CDG/TRS/MUC/FRA/MEX

ETC

Posted by lenze on January 09, 2002 2:47 PM

Try This:

Use Text to columns with the backslash as the delimiter, then use Concatenate in another column to rejoin the cells in reverse order.

Posted by Russell Hauf on January 09, 2002 3:52 PM

That's a tough one with a formula! How about a user-defined function? Paste the following code into a module in your workbook (if you don't know how to do this, let me know and I will explain). Nothing a little recursion can't solve!

Hope this helps,

Russell

```Option Explicit

Public Function ReverseTrip(rng As Range) As String     'Application.VolatileIf rng.Cells.Count > 1 Then         ReverseTrip = "N/A" Exit Function Else         ReverseTrip = ReverseSlash(Trim(rng.Text)) End If
End Function

Private Function ReverseSlash(strText As String) As String     Dim intSlash As Integer Dim strTemp As String     intSlash = InStr(strText, "/") If intSlash > 0 Then         strTemp = Left(strText, intSlash - 1)         ReverseSlash = ReverseSlash(Right(strText, Len(strText) - intSlash)) & "/" & strTemp Else         strTemp = strText         ReverseSlash = strText End If
End Function
```

Posted by Russell Hauf on January 10, 2002 8:09 AM

Pls let me know if you need further help...

Feel free to email me or post a message... intSlash = InStr(strText, "/") If intSlash > 0 Then strTemp = Left(strText, intSlash - 1) ReverseSlash = ReverseSlash(Right(strText, Len(strText) - intSlash)) & "/" & strTemp strTemp = strText ReverseSlash = strText End If