Excel

Remove the square brackets from an exact match keyword list using the SUBSTITUTE formula in Excel

0
2 years ago
by Peter Moore in

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


aaabbbccc

How to concatenate a column of text data into a comma seperated list

0
2 years ago
by Peter Moore in

How to concatenate a column of data into a comma seperated list. After you’ve finished your keyword research, you might want to paste the list of keywords back into AdWords as a comma separated list.

1/ open notepad 2

2/ paste in your column of data

3/ click ‘file’ anc change the ‘line endings’ to UNIX

4/ use ctrl + H to oopen find and replace

5/ input ‘\n’ in the search string box to find all line endings,  replace with ‘,’ in the replace with box.

6/ make sure the ‘Transform backslashes’ box is ticked

7/ click replace all

8/ remove whitespace as well using the remove whitespace function!

Go to Top