Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformatting text....
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformatting text....
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformatting text....
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" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reformatting text....
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 "Tom Ogilvy" skrev i melding ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
retain custom date format "text" when pasting or reformatting data | Excel Discussion (Misc queries) | |||
Help With Reformatting | Excel Discussion (Misc queries) | |||
Change default text color without reformatting the sheet. | Excel Discussion (Misc queries) | |||
Reformatting text in cells | Excel Worksheet Functions | |||
Reformatting dates as text | Excel Worksheet Functions |