Reformatting text....

[Titten Tei]

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

 

[Tom Ogilvy]

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

 

[Helmut Weber]

Hi Tom,

excellent!

Plus the capitalization:

[snip]
SwitchOrder = Join(v, " + ")
Mid(SwitchOrder, 1, 1) = UCase(Mid(SwitchOrder, 1, 1))
For lCnt = 1 To Len(SwitchOrder) - 1
If Mid(SwitchOrder, lCnt, 1) = " " Then
Mid(SwitchOrder, lCnt + 1, 1) = _
UCase(Mid(SwitchOrder, lCnt + 1, 1))
End If
Next
[snip]

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Titten Tei]

I still have some problems with the script.
It fails at the following occassions:

1.If a name does not contain a coutry code (like (USA) f.ex), it will not
switchorder.

2. If a name has country code but not contain a comma - you will get an
error message.

How can this be solved ?

Best regards
T.Tei