All text lowercase before a specific character

Brian F

Active Member
Joined
Aug 31, 2009
Messages
256
Excel 2010, Windows 8: I have this code that changes all of the text in a range to Not Bold, then goes through the range line by line and changes everything before a colon to Bold (a colon appears in each statement). At the present time each word before the colon begins with a Capital letter. I would now like to make everything before the colon lowercase . Can someone help me with this?

Sub Bold_To_Colon()

'Makes everything NOT bold then,
'Makes all the text before the colon bold

Dim c As Range, x As Long

Range("AS18:CC617").Select
Selection.Font.Bold = False

For Each c In Range("AS18:AS" & Range("AS" & Rows.Count).End(xlUp).row)
x = InStr(c, ":")
c.Font.Bold = False
If x > 0 Then c.Characters(1, x).Font.Bold = True
Next
End Sub
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Untested:

Code:
Sub BoldToColon()
    Dim cell        As Range
    Dim i           As Long

    Range("AS18:CC617").Font.Bold = False

    With Range("AS18", Cells(Rows.Count, "AS"))
        .Font.Bold = False

        For Each cell In .Cells
            i = InStr(cell.Value, ":")

            If i Then
                cell.Value = LCase(Left(cell.Value, i)) & Mid(cell.Value, i + 1)
                cell.Characters(1, i).Font.Bold = True
            End If
        Next cell
    End With
End Sub
 
Upvote 0
That works perfectly! Thank you so much shg. I now need to make the fist letter of each string a CAP. Do you have the time to show me how?
 
Upvote 0
Still untested:

Code:
Sub BoldToColon()
    Dim cell        As Range
    Dim i           As Long
    Dim s           As String

    Range("AS18:CC617").Font.Bold = False

    With Range("AS18", Cells(Rows.Count, "AS"))
        .Font.Bold = False

        For Each cell In .Cells
            s = cell.Value
            i = InStr(s, ":")

            If i Then
                s = UCase(Left(cell.Value, 1)) & _
                    LCase(Mid(s, 2, i - 1)) & _
                    Mid(cell.Value, i + 1)
                cell.Value = s
                cell.Characters(1, i).Font.Bold = True
            End If
        Next cell
    End With
End Sub
 
Upvote 0
Again, that works perfectly. While I was waiting I figured out a solution for myself but yours is vastly more elegant. Thank you for your help. That wraps up a project for me.
 
Upvote 0
Clenaup:

Code:
Sub BoldToColon()
    Dim cell        As Range
    Dim i           As Long
    Dim s           As String

    Range("AS18:CC617").Font.Bold = False

    With Range("AS18", Cells(Rows.Count, "AS"))
        .Font.Bold = False

        For Each cell In .Cells
            s = cell.Value
            i = InStr(s, ":")

            If i Then
                s = UCase(Left(s, 1)) & _
                    LCase(Mid(s, 2, i - 1)) & _
                    Mid(s, i + 1)
                cell.Value = s
                cell.Characters(1, i).Font.Bold = True
            End If
        Next cell
    End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top