MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Removing characters from cell

Posted by Brian Legate on September 25, 2001 12:01 PM

I need to develop an Excel macro that removes characters but leaves some text in the cell.
Example Before:
html coding-< font color=navy >< B >Sent to Production

Example After:
Sent to Production

I've know VB6 but I'm unfamilar with developing VBA macros.

Any advice would be helpful...

Posted by Jay on September 25, 2001 2:21 PM

It's hard to say exactly how to do it without knowing what, if anything, is common to the various input strings. For example, if each string has a "Bold" tag (< B >) just before the part you want to keep, the following would do it;

Sub RightTrim()

ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],LEN(RC[-1])-FIND(""< B >"",RC[-1])-4)"

End Sub

This example simply creates a formula in cell B1 that will put everything to the right of "< B >" in the text that is in cell A1.

Putting the following in cell B1 (with the input in A1) would do the same thing;

=RIGHT(A1,LEN(A1)-FIND("< B >",A1)-4)

FIND("< B >",A1) finds the first character of < B > in whatever is in A1 (so the "-4" is needed so all of "< B >" is removed).

There is plenty more that would be needed (like error handling), but maybe this will get you started.