Reversing the Order of a String

[carl]

I have a string like so:

ab;cd;ef;gh

is there a way to reverse the order ? eg:

gh;ef;cd;ab

Thank you in advance

 

[Frank Kabel]

Hi
would VBA be feasible for you?

 

[Don Guillett]

if always in the same place
=MID(E1,10,2)&MID(E1,6,3)&MID(E1,3,4)&MID(E1,1,2)

 

[carl]

hi frank and thanks. VBA would be fine.

 

[Ron Rosenfeld]

I have a string like so:

ab;cd;ef;gh

is there a way to reverse the order ? eg:

gh;ef;cd;ab

Thank you in advance

Easy with VBA.

<alt-F11> opens the Visual Basic Editor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens.

To use this, enter the function =REV(string, separator) into some cell. String
and Separator can either be actual strings, or cell references that contain
strings. As written, each variable must be in a single cell. But the UDF can
be rewritten if that is not suitable.

=======================
Function Rev(str As String, sep As String) As String
Dim temp, temp1
Dim i As Integer

temp = Split(str, sep)
ReDim temp1(UBound(temp))

For i = 0 To UBound(temp)
temp1(UBound(temp) - i) = temp(i)
Next i

Rev = Join(temp1, sep)

End Function
========================


--ron

 

[hrlngrv - ExcelForums.com]

Ron Rosenfeld wrote..
..

Function Rev(str As String, sep As String) As Strin

..
temp = Split(str, sep
..
Rev = Join(temp1, sep
End Functio
..

Should avoid Split and Join unless the OP mentions that s/he use
Excel 2000 or later. You udf won't work in Excel 97 or 95. For tha
matter, Split and Join can only handle single character separators
Adequate for the OP's sample data, but if you're going to write
udf, it might as well be as general as possible

For literal, possibly multiple character separator strings

Function rf(s As String, sep As String) As Strin
Dim k As Long, n As Long, p As Long, q As Lon

n = Len(s
k = Len(sep
q = n - k +

For p = q To 1 Step -
If Mid(s, p, k) = sep Or p = 1 The
If p = 1 The
rf = rf & Mid(s, p, q + 1
Els
rf = rf & Mid(s, p + k, q - p) & se
q = p -
End I
End I
Next

End Functio

 

[Ron Rosenfeld]

Adequate for the OP's sample data, but if you're going to write a
udf, it might as well be as general as possible.

For literal, possibly multiple character separator strings,

Well, if you are going to go beyond the OP's request, and make it as general as
possible, you should allow for zero-length separator strings also.

===================================
Function Rev(str As String, Optional sep) As String
Dim temp, temp1
Dim i As Integer

If Not IsMissing(sep) Then

str = Replace(str, sep, Chr(255))
temp = Split(str, Chr(255))
ReDim temp1(UBound(temp))

For i = 0 To UBound(temp)
temp1(UBound(temp) - i) = temp(i)
Next i

Rev = Join(temp1, sep)

Else
Rev = StrReverse(str)
End If

End Function
===============================

And, if the OP has an earlier version of Excel with a version of VB prior to
VB6, he may emulate the VB6 string functions by the method shown in
http://support.microsoft.com/default.aspx?scid=kb;en-us;188007




--ron