ExcelBanter - error in code: capitalize first letter of a word

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - error in code: capitalize first letter of a word (https://www.excelbanter.com/excel-programming/357050-error-code-capitalize-first-letter-word.html)

error in code: capitalize first letter of a word

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

error in code: capitalize first letter of a word

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

error in code: capitalize first letter of a word

Hi,

Take a look at the worksheetfunction Proper()

HTH
Carim

error in code: capitalize first letter of a word

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

error in code: capitalize first letter of a word

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

error in code: capitalize first letter of a word

@ 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