MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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.Volatile
If 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