asked on

VBA - Capitalise first letter of each word

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?

mbizup

strconv("" & strYourString, vbProperCase )

andyw27

ASKER

Thanks, already tried that way. Unfortunately I need to preserve words that are all upper case.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andyw27

ASKER

Perfect thanks.

mbizup

Glad to help out.

plummet

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

mbizup

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.

plummet

Oh yeah. I should have read the question properly

aikimark

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

mbizup

aikimark,

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

aikimark

@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

mbizup

Strange. This is what I was getting:

? ucasefirstletter("this is my NAME")
this is my NAME

mbizup

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)

aikimark

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

mbizup

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

andyw27

ASKER

Good answer.