Excel - Macro Help (Capitalization of the ENTIRE first word)

[Roller]

I am currently using this macro to capitalize the first letter of the word in excel. . .

Sub CapFirstWord()
'by Ed Weber 09/19/07
' Select continuous rows in the same column which contain the target text
Dim ActSh As Worksheet
Set ActSh = ActiveSheet
Dim temp As String
Dim strText As String
Dim oCell
temp = ""
For Each oCell In Selection.Cells
If IsEmpty(oCell) = False Then
strText = Trim(oCell.Value)
temp = UCase(Mid(strText, 1, 1))
strText = temp & Mid(strText, 2, Len(strText) - 1)
oCell.Value = ""
oCell.Value = strText
temp = ""
End If
Next oCell
End Sub

What I want to do is capitalize the first entire WORD in a cell with a similar macro. Basically if the cell has multiple words in the cell, for example a last name, first name. . .I want the entire last name capitalized. So it would be something like. . .

Smith, John

When you run the macro on selected cells it would change it to. . .

SMITH, John


Any advice/help is greatly appreciated! Thank you!!!

[Xan]

This macro will capitalize the first letter of any selected cell in Excel.try here
How to capitalize first letter of every word in Excel

Edit: Try this too:

Convert text to uppercase/lowercase using Macros

[Roller]

Not asking for capitalizing the first letter in the word in a given cell.

The macro code I pasted already does that. I want to know how to capitalize the ENTIRE FIRST WORD in any given cell with the use of a macro.

[Roller]

Is there anyone that could possibly adapt the code in the OP so that it capitalizes the first entire WORD and not just simply the first letter?

Thank you in advance for any help!

[RasMus]

Sample macro code to change the case of text in Excel

Macro to Change All Text in a Range to Uppercase Letters

Sub Uppercase()
   ' Loop to cycle through each cell in the specified range.
   For Each x In Range("A1:A5")
      ' Change the text in the range to uppercase letters.
      x.Value = UCase(x.value)
   Next
End Sub

Macro to Change All Text in a Range to Lowercase Letters

Sub Lowercase()
   ' Loop to cycle through each cell in the specified range.
   For Each x In Range("B1:B5")
      x.Value = LCase(x.Value)
   Next
End Sub

Macro to Change All Text in a Cell Range to Initial Capital Letters

Sub Proper_Case()
   ' Loop to cycle through each cell in the specified range.
   For Each x In Range("C1:C5")
      ' There is not a Proper function in Visual Basic for Applications.
      ' So, you must use the worksheet function in the following form:
      x.Value = Application.Proper(x.Value)
   Next
End Sub

[Xan]

Try this too:

Convert text to uppercase/lowercase using Macros

[Roller]

Thank you for your quick replies.

ALTHOUGH. . .

I'm still looking to just simply capitalize the first word in each cell. The entire first word, not all of it and not just the first letter.

For example;

Smith, John.

-to-

SMITH, John.

Thanks in advance! I appreciate the help, just hope this is possible through a quick dirty macro of some sort.