vbProperCase - multi hyphenated names

tf37

Board Regular
Joined
Apr 16, 2004
Messages
166
Need just a little bit of help here
I found the following code to help resolve hyphenated names, but need to tweak when you have more than one hyphen in a name
ie: the name is mary-joe jane > it works okay Mary-Joe Jane, but if the name is: mary-joe-jane smith it does Mary-Joe-jane Smith
I have a private sub and it calls the public function:

Private Sub PayTo_AfterUpdate()
' 09-16-20 tf attempt to have pay to name in proper case without manually doing it
' appears to work properly - yea!!
' does not handle hyphenated names
' found code to take care of hyphenated names and call a function

'Me.PayTo = StrConv(Me.PayTo, vbProperCase)
Me.PayTo = fProperCase(PayTo.Text)
End Sub


Public Function fProperCase(ByVal vName As String)
'received from jasonlewis and modified by redrumba on 10/26/2007
'Returns ProperCase, including hyphenated names and names with an apostrophe
'(i.e. bob->Bob; smith-jones; Smith-Jones and O'neill -> O'Neill)
Dim vReturn
Dim vLeft
Dim vRight
Dim lHyphen As Long
Dim lApostrophe As Long

vReturn = Null
lHyphen = Nz(InStr(1, vName, "-", vbBinaryCompare), 0)
lApostrophe = Nz(InStr(1, vName, "'", vbBinaryCompare), 0)

If Len(vName) Then

If lHyphen Then
vLeft = Mid(vName, 1, lHyphen - 1)
vRight = Mid(vName, lHyphen + 1)
vReturn = StrConv(vLeft, Conversion:=vbProperCase) & "-" & StrConv(vRight, Conversion:=vbProperCase)

Else
If lApostrophe Then
vLeft = Mid(vName, 1, lApostrophe - 1)
vRight = Mid(vName, lApostrophe + 1)
vReturn = StrConv(vLeft, Conversion:=vbProperCase) & "'" & StrConv(vRight, Conversion:=vbProperCase)
Else
vReturn = StrConv(vName, Conversion:=vbProperCase)
End If
End If

End If
fProperCase = vReturn
End Function
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
Hi TF
The built in function proper() works with hyphenated names for me, I'm using Excel 365.
If this is a new thing, an alternative is:
Excel Formula:
=SUBSTITUTE(PROPER(SUBSTITUTE(A1,"-","- ")),"- ","-")
This has the effect of putting a space in front of all the names following a hyphen then doing the proper case then removing the space again.
 

tf37

Board Regular
Joined
Apr 16, 2004
Messages
166
Thanks, I'll give something like that a try, but I'm using Access, not Excel : )
And it's an older version of Access from Office 2000.
 

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
oh sorry, I assumed excel. In Access there's a replace function which works pretty much the same as substitute in Excel and I think there's a proper in Access, but it may be called something else. Had a quick look, there's strconv with an option for proper case usage StrConv(<string variable>, vbProperCase)
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,651
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

oh sorry, I assumed excel.
The question is posted in the "Microsoft Access" forum. If you came across this thread in the "Unanswered Threads" listing, note that the forum the thread is found in is right under the thread title in that listing.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
vName = Replace(vName,"-","") ?

I might be missing the point because if you want to replace apostrophes as well, I'm not seeing a need to do stuff like Instr to find them and then use functions like Mid to remove them... :unsure:

EDIT - thought about that some more. So I suppose that is being done in order to upper case O and N in O Neil?
 
Last edited:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Maybe it's as simple as
VBA Code:
Function FixNames(vName As String) As String

FixNames = Replace(vName, "-", " ")
FixNames = Replace(FixNames, "'", " ")
FixNames = StrConv(FixNames, 3)
Debug.Print FixNames

End Function
OUTPUT:
fixnames("mary-joe-jane smith")
Mary Joe Jane Smith

fixnames("robert o'neil")
Robert O Neil

Depends on how it's being called/used or whether or not to remove the space in o'neil.
Your original problem might be caused by passing byVal or even using the Text property of the combo. Text is more difficult/dangerous than using its Value property (the default). Also, byVal creates a copy of the variable which then dies along with the procedure. If not handled correctly, you can lose it. Another potential issue is if you use the passed argument variable (vName) in the first line and set the function value to whatever you did to modify it, then use the variable thereafter, you're referring to the unmodified version of it. You'll notice that subsequently I used the function result instead. To make things a bit more intuitive and in the interest of reliability, I would create a variable and process that rather than use the function name to modify it further. At this point, I'm just playing around so I took an approach that I wanted to pare down as much as possible yet work according to how I interpret your post.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
443
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
OUTPUT:
fixnames("mary-joe-jane smith")
Mary Joe Jane Smith

fixnames("robert o'neil")
Robert O Neil

I think the OP wants to keep the hyphen, but your code gave me an idea...

VBA Code:
Function FixName(strName As String) As String

FixName = Replace(strName, "-", Chr(0))
FixName = StrConv(FixName, vbProperCase)
FixName = Replace(FixName, Chr(0), "-")

End Function

Code:
?fixname("mary-joe-jane smith")
Mary-Joe-Jane Smith
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,058
Office Version
  1. 365
Platform
  1. Windows
Maybe we'll never know. Even after reviewing the OP and the notes within the code I can't make definitive sense of what the goals are. The post title is about hyphens but there's this mix of capitalization in there, and I couldn't tell what from what. I mean who knows at this point - what if the last pair was hyphenated? To my mind, the post doesn't have enough examples and desired outputs. I'd expect Mary-Joe to be hyphenated but not Joe-Jane for that matter. Anyway, I was just trying to show that based on what the sample code does, it appears that it can be condensed quite a bit.
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
443
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Couldn't get the apostrophe to play nice, but perhaps just replacing it with a different character is the way to do it....

VBA Code:
Function FixName(strName As String) As String

    FixName = Replace(strName, "-", Chr(0))
    FixName = Replace(FixName, "'", Chr(11))
    FixName = StrConv(FixName, vbProperCase)
    FixName = Replace(FixName, Chr(0), "-")
    FixName = Replace(FixName, Chr(11), "'")

End Function

Code:
?fixname("mary-joe-jane smith o'niel")
Mary-Joe-Jane Smith O'Niel

All of this assumes you wouldn't have the Chr(0) (Null) or Chr(11) (vertical tab) in your name field - and I genuinely doubt that you would...
 

Watch MrExcel Video

Forum statistics

Threads
1,127,562
Messages
5,625,525
Members
416,116
Latest member
Joemamasuka

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top