Change Text Case But ONLY Within (****) Brackets

[KennyUK]

Hi Guys,

I have a bit of a problem that I wondered if anyone can help me with.

I need to be able to change the case of text contained inside brackets to Sentence Case (Each Word Starts With A Capital Letter. I need it to ignore any text before or after the ( ). It would be best for me if this could be used as a macro or addin.

Here is an example of what I need:

Cell A1 = Hello There (how are you today)
Cell A2 = The (sun) is hot

Want to change to:

Cell A1 = Hello There (How Are You Today)
Cell A2 = The (Sun) is hot

Any ideas or suggestions would be gratefully accepted.

My thanks in advance,

Kenny

 

[Glaswegian]

Have a look at this site
http://www.asap-utilities.com/

or this thread
http://www.mrexcel.com/board2/viewtopic.php?t=81091

Should get you started.

Regards

 

[Richie(UK)]

Hi Kenny,

How about:

Sub ISentenceYou()
    Dim rngText As Range, rngCell As Range
    Dim lStart As Long, lEnd As Long, strTemp As String
    
    Application.ScreenUpdating = False
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rngText = .Range("A1:A" & .Cells(.Rows.Count, "A").End(xlUp).Row) _
            .SpecialCells(xlCellTypeConstants, xlTextValues)
        For Each rngCell In rngText
            lStart = 0: lEnd = 0
            With rngCell
                lStart = InStr(1, .Text, "(")
                If lStart > 0 Then
                    lEnd = InStr(lStart, .Text, ")")
                    If lEnd > 0 Then
                        strTemp = Left(.Text, lStart) & _
                            Application.WorksheetFunction.Proper _
                            (Mid(.Text, lStart + 1, lEnd - (lStart + 1))) & _
                            Right(.Text, Len(.Text) - (lEnd - 1))
                        .Value = strTemp
                    End If
                End If
            End With
        Next rngCell
    End With
    
    Application.ScreenUpdating = True
    
End Sub

You could amend the range definition to use Selection if you wanted something more general-purpose.

HTH

 

[Krishnakumar]

Hi,

A formula based approach.

=LEFT(A1,FIND("(",A1)-1)&(PROPER(MID(A1,FIND("(",A1),(FIND(")",A1)-FIND("(",A1)+1)))&RIGHT(A1,LEN(A1)-FIND(")",A1)))