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

[Kenny2003]

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

 

[PaulRD]

Public Sub proper()
Dim OldString As String
Dim NewString As String
Dim posA As Integer
Dim posB As Integer

OldString = ActiveCell.Value
posA = InStr(OldString, "(")
posB = InStr(OldString, ")")
NewString = Left(OldString, posA) & _
            Application.proper(Mid(OldString, posA + 1, posB - posA)) & _
            Right(OldString, Len(OldString) - posB)
ActiveCell.Value = NewString
End Sub

Paul D
pimpin' aint easy

 

[Kenny2003]

Hi Paul,

Thank you so much for taking the time to reply to my post.

Can you assume that I am a relitive novice about these things and could I ask you if you would kindly explain exactly what I need to do in order to use the code.

Your help is much appreciated,

Thank you in advance,

Kenny

 

[dhulbert]

Kenny.

If you go to tools-macros-visual basic editor
then add a new module using inset-module
you can copy and paste Paul's code into the module but if you cannge the work Sub to say Function then you can use the code as a formula in the format
=Proper(a2)
Should do what you want.

 

[ManxDom]

Hi Kenny
paste this into a macro then run it.
I have kept most of the code that Paul supplied the same
the only difference being is this will work down column A for 10 rows(this can be changed of course).
This should give you the idea of how it works.

Sub macro1()
Dim OldString As String
Dim NewString As String
Dim posA As Integer
Dim posB As Integer
Dim loopctr As Integer

For loopctr = 1 To 10
OldString = Cells(loopctr, 1).Value
posA = InStr(OldString, "(")
posB = InStr(OldString, ")")
NewString = Left(OldString, posA) & _
UCase((Mid(OldString, posA + 1, posB - posA))) & _
Right(OldString, Len(OldString) - posB)
Cells(loopctr, 1).Value = NewString
Next loopctr
End Sub

Dom

 

[PaulRD]

Kenny2003,
As you can see, there are numerous ways to apply this macro, either by running the routine or creating a function with it. If you give specifics of why or what your intent is on using a macro to change the text in the cells, we can offer more assistance.

Another example not shown is having it loop through a selected range.

Paul D
pimpin' aint easy