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(xlCel lTypeConstants, 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

--------------------


THANKS A LOT!!!


--
JVLennox
------------------------------------------------------------------------
JVLennox's Profile: http://www.excelforum.com/member.php...o&userid=32505
View this thread: http://www.excelforum.com/showthread...hreadid=526143

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"
wrote in
message
...

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(xlCel lTypeConstants,
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

--------------------


THANKS A LOT!!!


--
JVLennox
------------------------------------------------------------------------
JVLennox's Profile:
http://www.excelforum.com/member.php...o&userid=32505
View this thread:
http://www.excelforum.com/showthread...hreadid=526143

Hi,

Take a look at the worksheetfunction Proper()

HTH
Carim

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


--
Don Guillett
SalesAid Software

"JVLennox" wrote in
message ...

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(xlCel lTypeConstants, 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

--------------------


THANKS A LOT!!!


--
JVLennox
------------------------------------------------------------------------
JVLennox's Profile:
http://www.excelforum.com/member.php...o&userid=32505
View this thread: http://www.excelforum.com/showthread...hreadid=526143

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

--
Regards,
Tom Ogilvy

"JVLennox" wrote:


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(xlCel lTypeConstants, 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

--------------------


THANKS A LOT!!!


--
JVLennox
------------------------------------------------------------------------
JVLennox's Profile: http://www.excelforum.com/member.php...o&userid=32505
View this thread: http://www.excelforum.com/showthread...hreadid=526143

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(xlCel lTypeConstants, 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
------------------------------------------------------------------------
JVLennox's Profile: http://www.excelforum.com/member.php...o&userid=32505
View this thread: http://www.excelforum.com/showthread...hreadid=526143

@ Tom

GREAT!!!

Works PERFECTLY!!!!

Thank you all!


--
JVLennox
------------------------------------------------------------------------
JVLennox's Profile: http://www.excelforum.com/member.php...o&userid=32505
View this thread: http://www.excelforum.com/showthread...hreadid=526143