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-&LT; font color=navy >&LT; 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...
Thanks,
Brian

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 (&LT; B >) just before the part you want to keep, the following would do it;

Sub RightTrim()

Range("B1").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-1],LEN(RC[-1])-FIND(""&LT; B >"",RC[-1])-4)"

End Sub

This example simply creates a formula in cell B1 that will put everything to the right of "&LT; 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("&LT; B >",A1)-4)

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

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

Jay