I have this:

Function ctTxt(cvTxt As String)
ctTxt = Left(UCase(cvTxt), 1) & "" & Right(cvTxt, Len(cvTxt) - 1)
End Function

Which takes the cell which contains.

hello free world and converts it to Hello free world.

... but how can I make the function loop to say

Hello Free World.

every first letter capitalized.
i'm think combining the Len() and Instr() function to create some type
of loop, but it seems that I'm stuck.

Hi Vincent,

Try this

Function ctTxt(cvTxt As String)
ctTxt = Application.WorksheetFunction.Proper(cvTxt)
End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Vincent Jones" wrote in message
om...
I have this:

Function ctTxt(cvTxt As String)
ctTxt = Left(UCase(cvTxt), 1) & "" & Right(cvTxt, Len(cvTxt) - 1)
End Function

Which takes the cell which contains.

hello free world and converts it to Hello free world.

.. but how can I make the function loop to say

Hello Free World.

every first letter capitalized.
i'm think combining the Len() and Instr() function to create some type
of loop, but it seems that I'm stuck.

Vincent,

two ways.

first
if you string is in cell A1 put this formula in another cell
=proper(A1)

second
using code

Sub ChangeCase()
Range("a1").Value = WorksheetFunction.Proper(Range("a1").Value)
End Sub

Neil

"Vincent Jones" wrote in message
om...
I have this:

Function ctTxt(cvTxt As String)
ctTxt = Left(UCase(cvTxt), 1) & "" & Right(cvTxt, Len(cvTxt) - 1)
End Function

Which takes the cell which contains.

hello free world and converts it to Hello free world.

.. but how can I make the function loop to say

Hello Free World.

every first letter capitalized.
i'm think combining the Len() and Instr() function to create some type
of loop, but it seems that I'm stuck.