Sub Tester()
Dim s As String
s = "ADAMS, BRYAN (ENG) + " & _
"DENVER, JOHN (USA) + JARRE, " & _
"JEAN MICHELLE (FRA) + " & _
"THE BEATLES + THE DOORS"
MsgBox SwitchOrder(s)
End Sub
Public Function SwitchOrder(s)
Dim v As Variant, s1 As String
Dim s2 As String, s3 As String
Dim s4 As String, s5 As String
Dim iloc As Long, iloc1 As Long
v = Split(s, "+")
For i = LBound(v) To UBound(v)
s1 = Trim(v(i))
iloc = InStr(1, s1, "(", vbTextCompare)
If iloc < 0 Then
s5 = Trim(Right(s1, Len(s1) - iloc + 1))
s2 = Left(s1, iloc - 1)
iloc1 = InStr(1, s2, ",", vbTextCompare)
s3 = Trim(Left(s2, iloc1 - 1))
s4 = Trim(Right(s2, Len(s2) - iloc1))
s4 = s4 & " " & s3 & " " & s5
v(i) = s4
End If
Next

SwitchOrder = Join(v, " + ")
End Function

--
Regards,
Tom Ogilvy


"Titten Tei" wrote:

Hi.
First, I'm not sure if this is the correct newsgroup for this but I try...

I would like to use excel to reformat text in a musiclibrary.

All performers are located in a textfile like this:

Line 1: ADAMS, BRYAN (ENG) + DENVER, JOHN (USA) + JARRE, JEAN MICHELLE (FRA)
+ THE BEATLES + THE DOORS

I want to acchive a swithching of lastname, firstname so result being like
the following :
(If possible change the letters to capital exept for
the first letter. The language code ex. (ENG) should be kept in capital.)

Bryan Adams (ENG) + John Denver (USA) + Jean Michelle Jarre (FRA) + The
Beatles + The Doors

Is there an expert who knows the answer in this case ?

\T. Tei