error in code: capitalize first letter of a word

[JVLennox]

Hi everybody,

I want to captalize the first letter of the FIRST word in each cell of
a column.

The cell contains something like this:
a anterior view
b first cervical vertebra (atlas)
...

I'd like to habe the FIRST LETTER OF THE FIRST WORD capitalized.
(not the first letter a, b or c)

I have this code, but it does it for ALL words.

How can one stop it from doing that?


Code:
--------------------

Sub First_Letter_Cap()
i = CInt(InputBox("Which column" & vbLf & " A=1, B=2,..", "Question", "1"))
For Each cell In Worksheets("Sheet2").Columns(i).SpecialCells(xlCellTypeConstants, 2)
strText = cell.Value
Trennzeichen = " "
posStart = InStr(1, strText, Trennzeichen)
part2 = Mid(strText, posStart + 1)
part1 = Left(strText, posStart)
cell.Value = part1 & Trim(WorksheetFunction.Proper(part2))
Next cell
End Sub

 

[Chip Pearson]

Dim Rng As Range
For Each Rng In Selection.Cells
Rng.Value = UCase(Left(Rng.Text, 1)) & Mid(Rng.Text, 2)
Next Rng



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"JVLennox"
message
news:[email protected]...

 

[Carim]

Hi,

Take a look at the worksheetfunction Proper()

HTH
Carim

 

[Don Guillett]

try
Sub capitalizethirdchar()
For Each c In Selection
MsgBox Left(c, 2) & UCase(Mid(c, 4, 1)) & Right(c, Len(c) - 2)
Next
End Sub

 

[Tom Ogilvy]

Sub First_Letter_Cap()
i = CInt(InputBox("Which column" & vbLf & _
" A=1, B=2,..", "Question", "1"))
For Each cell In Worksheets("Sheet2") _
.Columns(i).SpecialCells(xlCellTypeConstants, 2)
strText = cell.Value
Trennzeichen = " "
posStart = InStr(1, strText, Trennzeichen)
cell.Characters(posStart + 1, 1).Text = _
UCase(cell.Characters(posStart + 1, 1).Text)
Next cell
End Sub

 

[JVLennox]

Hey guys thanks!

But I forgot something:

sometimes a cell can be like this:
a-c second thoracic vertebra.
d cervical spine with both vertebral arteries and the emerging spinal
nerves
e schematic coronal section, tenth week.

So, the seperator must be " " and then only the FIRST WORD shall be
capitalized.

But my code does ALL words... :-()

Code:
--------------------

Sub First_Letter_Cap()
i = CInt(InputBox("Which column" & vbLf & " A=1, B=2,..", "Question", "1"))
For Each cell In Worksheets("Sheet2").Columns(i).SpecialCells(xlCellTypeConstants, 2)
strText = cell.Value
Trennzeichen = " "
posStart = InStr(1, strText, Trennzeichen)
part2 = Mid(strText, posStart + 1)
part1 = Left(strText, posStart)
cell.Value = part1 & Trim(WorksheetFunction.Proper(part2))
Next cell
End Sub

 

[JVLennox]

@ Tom

GREAT!!!

Works PERFECTLY!!!!

Thank you all!