Capitilize last word in cell

[MikeTi]

Hi,
I am trying to find a way to capitalize the last word in a cell.
frequently get
names formatted where A1 = First Name A2 = Last name or A1=jan A2=va
der hass and need to capitalize the h in hass. I have not been able t
find a way to accomplish this. Your help is greatly appreciated

 

[Claus Busch]

Hi Mike,

Am Mon, 7 May 2012 17:29:06 +0000 schrieb MikeTi:

I am trying to find a way to capitalize the last word in a cell. I
frequently get
names formatted where A1 = First Name A2 = Last name or A1=jan A2=van
der hass and need to capitalize the h in hass. I have not been able to
find a way to accomplish this. Your help is greatly appreciated.

the simplest way is to do it with VBA.
Formula:
=LEFT(A2,FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ",)))))&PROPER(SUBSTITUTE(A2,LEFT(A2,FIND("#",SUBSTITUTE(A2," ","#",LEN(A2)-LEN(SUBSTITUTE(A2," ",))))),))
Macro:

Sub Capitalize()
Dim Start As Integer
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
Start = InStrRev(rngC, " ")
rngC = Left(rngC, Start) & _
WorksheetFunction.Proper(Right(rngC, Len(rngC) - Start))
Next
End Sub


Regards
Claus Busch