Change first letter of word to uppercase

[elsuji]

Dear Team,

I am having 2 sheet on my workbook ( "Data" & "Sheet3" )

I am typing vale on "Data" and copying to "Sheet3" using =Data!C4 formula where the cells (B5:F6) which is merged.

I want to update all the first letter uppercase on "Sheet3"

I am writing the following code on "Sheet3"

Sub CapsFirstLetter()Dim Cell As Range


With ActiveSheet
For Each Cell In Application.Intersect(.UsedRange, .Range("B5:F6")).Cells
Cell.Formula = UCase(Left(Cell.Text, 1)) & LCase(Mid(Cell.Text, 2))
Next Cell
End With


End Sub

But it is not working.

Can any one please help me to solve this

[gmayor]

You are probably overthinking this. Try the following


Sub CapsFirstLetter()
Dim oCell As Range
With Sheets("Sheet3")
For Each oCell In .Range("B5:F6").Cells
oCell = UCase(Left(oCell.Text, 1)) & LCase(Mid(oCell.Text, 2))
Next oCell
End With
End Sub

[elsuji]

Dear Graham Mayor,

Thanks for your reply. Your code is working.

But there is one problem

In "Data" my value is M/s PANCHAMI CONCRETE. As per your code it is updating M/s panchami concrete.

But i want it should update M/s Panchami Concrete (upper case of all the first word)

[snb]

Avoid merged cells at all costs.

Use VBA:


MsgBox StrConv("aaaa bbbb ccccc", 3)

[avichandana]

use "Proper" formula

M/s PANCHAMI CONCRETE : =Proper(cell containing the text): M/s Panchami Concrete