Solved

VBA - Capitalise first letter of each word

Posted on 2011-09-07
16
689 Views
Last Modified: 2012-06-27
Hi,

I have need to ca capitalise the first letter of each word with a string.  I’ve had some success using VBProper however this changes the entire string.

For example this string ‘test data for a WORD’

Becomes ‘Test Data For A Word’

I need a function which does not convert any other letter than the first.  So I need a function that word produce the following string:

Test Data For A WORD

Any suggestions?
0
Comment
Question by:andyw27
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 3
  • +1
16 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36494391
strconv("" & strYourString, vbProperCase )
0
 

Author Comment

by:andyw27
ID: 36494430
Thanks, already tried that way.  Unfortunately I need to preserve words that are all upper case.  
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36494450
Okay- understood now.  This will do the trick:

Function UCaseFirstLetter(s As String) As String
    Dim arr() As String
    Dim strTemp As String
    Dim I As Integer
    
    arr = Split(s, " ")
    For I = 0 To UBound(arr)
        arr(I) = UCase(Left(arr(I), 1)) & Right(arr(I), Len(arr(I)) - 1)
        strTemp = strTemp & " " & arr(I)
    Next

    UCaseFirstLetter = Trim(strTemp)
    
End Function

Open in new window


You can test it like this:

debug.print Ucasefirstletter("this is a TEST")

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:andyw27
ID: 36494517
Perfect thanks.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36494578
Glad to help out.
0
 
LVL 10

Expert Comment

by:plummet
ID: 36494865
Or even simpler:

function UpperFirstLetter(sInput as string) as string

  if len(sInput)>0 then
    UpperFirstLetter=ucase$(left$(sInput,1)) & mid$(sInput,2)
  end if

end function

Open in new window


0
 
LVL 61

Expert Comment

by:mbizup
ID: 36494921
That doesn't quite do it (it only works for one word).  

<Capitalise first letter of each word>

The request here is to capitalize the first letter in each word of a string without affecting the other letters in those words.  
0
 
LVL 10

Expert Comment

by:plummet
ID: 36495010
Oh yeah. I should have read the question properly
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36499328
Starting with mbizup's routine:

Function UCaseFirstLetter(parmString As String) As String
    Dim arr() As String
    Dim I As Integer
    
    arr = Split(parmString, " ")
    For I = 0 To UBound(arr)
        If arr(I) = UCase(arr(I)) Then
        Else
            arr(I) = StrConv(arr(I), vbProperCase)
        End If
    Next

    UCaseFirstLetter = Join(arr)
    
End Function

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36499592
aikimark,

That takes away the functionality that made the first letter of each word uppercase.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36499668
@mbizup

It seems to get the (stated) desired results.  Unless the entire word is capitalized, then it applies a proper case transformation to the word.

?UCaseFirstLetter("test data for a WORD")
Test Data For A WORD

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36499981
Strange.  This is what I was getting:

? ucasefirstletter("this is my NAME")
this is my NAME
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36500016
Which makes sense...
You must have some setting in place for case sensitive comparisons.  

By default, string comparisons are not case sensitive, so this condition will always be true:

If arr(I) = UCase(arr(I)) Then

Open in new window


But you also need to account for cases like mcDonald, which should become McDonald (ie: only the first letter changes)
0
 
LVL 45

Expert Comment

by:aikimark
ID: 36500209
This version will force a case-sensitive comparison.

Function UCaseFirstLetter(parmString As String) As String
    Dim arr() As String
    Dim I As Integer
    
    arr = Split(parmString, " ")
    For I = 0 To UBound(arr)
        If StrComp(arr(I), UCase(arr(I)), vbBinaryCompare) = 0 Then
        Else
            arr(I) = StrConv(arr(I), vbProperCase)
        End If
    Next

    UCaseFirstLetter = Join(arr)
    
End Function

Open in new window


I should have thought about this since I wrote an article about this very subject.
http:/A_1897-The-Case-for-case-sensitive-Modules.html
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36500252
You're still going to run into issues with words/names with capitals inside the string, like 'mcDonald'

<I need a function which does not convert any other letter than the first.>

? UCaseFirstLetter ("old mcDonald had a FARM")
Old Mcdonald Had A FARM  '<-- the capital D gets converted to lower case
0
 

Author Closing Comment

by:andyw27
ID: 36505648
Good answer.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question