Consulting

Results 1 to 5 of 5

Thread: Change first letter of word to uppercase

  1. #1
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    102
    Location

    Change first letter of word to uppercase

    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

  2. #2
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Contributor
    Joined
    Jun 2019
    Posts
    102
    Location
    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)

  4. #4
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,774
    Avoid merged cells at all costs.

    Use VBA:

       MsgBox StrConv("aaaa bbbb ccccc", 3)

  5. #5
    use "Proper" formula

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •