Archive for December, 2009

You can use the SUBSTITUTE formula in excel to remove individual characters from a string of text by substituting the errant characters with nothing. This is useful if you need to remove the first and last character from a string of text in a cell.
Handy if you want to remove the square brackets from an exact match keyword list.

1/ First, you need to identify the character code of the characters you want to remove by using this formula into cell B1,
=CODE(LEFT(A2,1))

2/ Next, type ‘[' or the character you want to delete into the cell to the left of B1 (A1). This gives you the character code for your errant character - in this case the code for '[' is '91' and the code for ']‘ is ’93′.

3/ Then you need to trim the errant characters from your text string. To do this, paste your list into cell A1 of new tab and then paste this formula into cell B1 to trim the ‘[' character from your text string.
=TRIM(SUBSTITUTE(A1,CHAR(91),""))

4/ Remove the ']‘ character by pasting this formula into cell C1
=TRIM(SUBSTITUTE(B1,CHAR(93),”"))

useful_formulas