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

Hi
would VBA be feasible for you?

"carl" wrote:

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

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

--
Don Guillett
SalesAid Software

"carl" wrote in message
...
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

hi frank and thanks. VBA would be fine.

"carl" wrote:

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

On Mon, 6 Dec 2004 05:43:04 -0800, "carl"
wrote:

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

Ron Rosenfeld wrote...
...
Function Rev(str As String, sep As String) As String
...
temp = Split(str, sep)
...
Rev = Join(temp1, sep)
End Function
...

Should avoid Split and Join unless the OP mentions that s/he uses
Excel 2000 or later. You udf won't work in Excel 97 or 95. For that
matter, Split and Join can only handle single character separators.
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,


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

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

For p = q To 1 Step -1
If Mid(s, p, k) = sep Or p = 1 Then
If p = 1 Then
rf = rf & Mid(s, p, q + 1)
Else
rf = rf & Mid(s, p + k, q - p) & sep
q = p - k
End If
End If
Next p

End Function
---------
www.coffeecozy.com

Use your Bodum and give up cold coffee for good!

On Mon, 06 Dec 2004 11:30:08 -0800, lid
(hrlngrv - ExcelForums.com) wrote:

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...b;en-us;188007




--ron