Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert second word in range to Proper when first variable length -urgent please
Hi - thanks for having a look
I have 1 column of names: D (in a many column worksheet) with initials and surname in upper case e.g. AB PATTERSON or A PATTERSON and I want the surname only (not initials) as proper. Also there is the case of two people e.g. AB PATTERSON & B BANJO and hyphenated names - e.g. AB PATTERSON-POET How can I have a function or VBA asertain where the surname/s start and convert. Hope someone can help Cheers Peta |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert second word in range to Proper when first variable length - urgent please
Are there **always** initial in front of each name (single names and names
with & between them)? Is there **ever** any other symbol joining compound names besides & and dash? Do any names have multiple initials with spaces between them? Any other type of anomalies that you are aware of? Rick wrote in message ... Hi - thanks for having a look I have 1 column of names: D (in a many column worksheet) with initials and surname in upper case e.g. AB PATTERSON or A PATTERSON and I want the surname only (not initials) as proper. Also there is the case of two people e.g. AB PATTERSON & B BANJO and hyphenated names - e.g. AB PATTERSON-POET How can I have a function or VBA asertain where the surname/s start and convert. Hope someone can help Cheers Peta |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert second word in range to Proper when first variable length- urgent please
I've replied to Rick about an hour ago but it doesn't seem to have
posted. Apologies if this is a repeat. Are there **always** initials in front of each name (single names and names with & between them)? Yes (1 or 2 with no spaces between) Is there **ever** any other symbol joining compound names besides & and dash? No Do any names have multiple initials with spaces between them? No Any other type of anomalies that you are aware of? None Thanks very much Peta |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert second word in range to Proper when first variable length - urgent please
Nope, your first post didn't make it through. Okay, I think the following
will do what you want. Copy/Paste the code below into your worksheet's code window. There is one macro and one subroutine... the macro that you will call is named MakeNamesProper (it calls the subroutine as it needs to). I set code to operate on Column D in Sheet1; change the sheet reference in the With statement of MakeNamesProper to the name of the sheet with your names on them. Okay, that is it... go to your sheet and run the MakeNamesProper macro (Alt+F8)... it should convert all the name in Column D of that sheet to the format you asked for. Sub MakeNamesProper() Dim x As Long Dim LastRow As Long Dim Text As String Dim Parts() As String With Worksheets("Sheet1") LastRow = .Cells(Rows.Count, "D").End(xlUp).Row For x = 1 To LastRow Text = .Cells(x, "D").Value Parts = Split(Text, "&") IndividualName Parts(0) If InStr(Text, "&") Then IndividualName Parts(1) Text = Join(Parts, "&") .Cells(x, "D").Value = Text Next End With End Sub Sub IndividualName(Text As String) Dim Dash As Long Dim Space As Long Text = StrConv(Text, vbProperCase) If InStr(Text, "-") Then Dash = InStr(Text, "-") Mid(Text, Dash) = " " Mid(Text, Dash) = "-" End If Space = InStr(Text, " ") Mid(Text, 1) = UCase(Left(Text, Space - 1)) End Sub Rick wrote in message ... I've replied to Rick about an hour ago but it doesn't seem to have posted. Apologies if this is a repeat. Are there **always** initials in front of each name (single names and names with & between them)? Yes (1 or 2 with no spaces between) Is there **ever** any other symbol joining compound names besides & and dash? No Do any names have multiple initials with spaces between them? No Any other type of anomalies that you are aware of? None Thanks very much Peta |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert second word in range to Proper when first variable length- urgent please
On Mar 14, 2:56*pm, "Rick Rothstein \(MVP - VB\)"
wrote: Nope, your first post didn't make it through. Okay, I think the following will do what you want. Copy/Paste the code below into your worksheet's code window. There is one macro and one subroutine... the macro that you will call is named MakeNamesProper (it calls the subroutine as it needs to). I set code to operate on Column D in Sheet1; change the sheet reference in the With statement of MakeNamesProper to the name of the sheet with your names on them. Okay, that is it... go to your sheet and run the MakeNamesProper macro (Alt+F8)... it should convert all the name in Column D of that sheet to the format you asked for. Sub MakeNamesProper() * Dim x As Long * Dim LastRow As Long * Dim Text As String * Dim Parts() As String * With Worksheets("Sheet1") * * LastRow = .Cells(Rows.Count, "D").End(xlUp).Row * * For x = 1 To LastRow * * * Text = .Cells(x, "D").Value * * * Parts = Split(Text, "&") * * * IndividualName Parts(0) * * * If InStr(Text, "&") Then IndividualName Parts(1) * * * Text = Join(Parts, "&") * * * .Cells(x, "D").Value = Text * * Next * End With End Sub Sub IndividualName(Text As String) * Dim Dash As Long * Dim Space As Long * Text = StrConv(Text, vbProperCase) * If InStr(Text, "-") Then * * Dash = InStr(Text, "-") * * Mid(Text, Dash) = " " * * Mid(Text, Dash) = "-" * End If * Space = InStr(Text, " ") * Mid(Text, 1) = UCase(Left(Text, Space - 1)) End Sub Rick wrote in message ... I've replied to Rick about an hour ago but it doesn't seem to have posted. Apologies *if this is a repeat. Are there **always** initials in front of each name (single names and names with & between them)? Yes (1 or 2 with no spaces between) Is there **ever** any other symbol joining compound names besides & and dash? No Do any names have multiple initials with spaces between them? No Any other type of anomalies that you are aware of? None Thanks very much Peta- Hide quoted text - - Show quoted text - That's brilliant - saved me so much work. Just two little hiccups - my fault - didn't mention it apostrophe surnames & hyphenated: e.g. O'Brien-Radford (required) - got O'brien-radford. Thanks so much Rick |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
convert second word in range to Proper when first variable length - urgent please
Just two little hiccups - my fault - didn't mention it
apostrophe surnames & hyphenated: e.g. O'Brien-Radford (required) - got O'brien-radford. So, you lied to me... there was another linking character and/or anomaly, huh?<g No problem... as it turns out, I had left a statement out of the code I posted which means no hyphenated name would have been handled correctly. Replace all the code I gave you in my other response with the following code which will handle names with apostrophes and dashes correctly. You can run the macro against your existing data even if it contains properly formatted names... the routine will simply replace correctly formatted names with itself; but, in the meantime, it will straighten out dashed and apostrophe'd names. Sub MakeNamesProper() Dim X As Long Dim LastRow As Long Dim Apostrophe As Long Dim Text As String Dim Parts() As String With Worksheets("Sheet3") LastRow = .Cells(Rows.Count, "D").End(xlUp).Row For X = 1 To LastRow Text = .Cells(X, "D").Value Parts = Split(Text, "&") IndividualName Parts(0) If InStr(Text, "&") Then IndividualName Parts(1) Text = Join(Parts, "&") If InStr(Text, "'") Then Apostrophe = InStr(Text, "'") Mid(Text, Apostrophe + 1) = UCase(Mid(Text, Apostrophe + 1, 1)) End If .Cells(X, "D").Value = Text Next End With End Sub Sub IndividualName(Text As String) Dim Dash As Long Dim Space As Long Text = StrConv(Text, vbProperCase) If InStr(Text, "-") Then Dash = InStr(Text, "-") Mid(Text, Dash) = " " Text = StrConv(Text, vbProperCase) Mid(Text, Dash) = "-" End If Space = InStr(Text, " ") Mid(Text, 1) = UCase(Left(Text, Space - 1)) End Sub Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert word to excel with proper formatting | Excel Discussion (Misc queries) | |||
Variable Range Length & .FillDown? | Excel Programming | |||
Variable series length/range | Charts and Charting in Excel | |||
Copying RANGE of variable length | Excel Programming | |||
Using a Macro to Sum a Variable-Length Range (a Column) | Excel Programming |