Sleeper: Change text to upper case after specified symbols

[Dreamer]

Hi,

How can I change the text to upper case after some symbols like "*", "(",")","!"

for example, change *the to *The, (three) to (Three), etc.
It can find and replace all data in col B.


Thanks a lot.

[MWE]

Hi,

How can I change the text to upper case after some symbols like "*", "(",")","!"

for example, change *the to *The, (three) to (Three), etc.
It can find and replace all data in col B.


Thanks a lot.
Since you posted to a VBA forum, I assume you want a VBA solution. There are many ways to do this, one solution would be to:


sequence down column B
test the contents of each cell in col B ( Cell(I,2) if I is the sequence index) for the special characters (using, say, the instr function)
for each occurance of a special character, replace the character before it with its corresponding upper case.
NOTE: since you can have multiple instances of any special character in a given cell, your test should test for each special character, modify the string if found, and keep testing (starting with the "next" char position) until the instr function returns "not found"
stopping the process (sequencing down col B) will depend on your particular circumstances. If you wish to test all cells from, say, row 2 to the last populated row, your loop might be something like



For I = 2 to LastRow
...
...
Next I

see the KB (Knowledge Base) for how to find the last populated row in a worksheet