Add characters before or after every word in cell

In a previous article, we looked at how to add a symbol, word, or text to all cells in a column. That approach works for adding something once at the beginning or end of a cell. But sometimes, the task is different — you need to quickly insert a symbol before every word in a cell. In this case, the symbol will appear multiple times — once before each word. If the cell contains only one word, the two tasks are essentially the same.

One common example is in PPC advertising when you need to add a specific character before each keyword in a phrase. For example, adding an exclamation mark before every word to prevent search systems from matching synonyms. Other requests include adding commas after each word or placing every word in quotes. The latter actually means two consecutive operations — adding quotation marks before each word, then after (or the other way around).

Symbol before every word in a cell – Excel formula

For our purposes, let’s define a “word” as any set of characters in a cell that starts at the beginning of the string or follows a space, and ends at a space or the end of the string. That means if there are already symbols before the letters, we won’t insert after those symbols, but before the letters. To solve this, we need to handle four situations:

1. If there are no words in the cell — do nothing;
2. If the cell contains one word — just insert the symbol before it;
3. If there are 2 or more words — replace each space with a space and the symbol. This will add the symbol before each word except the first;
4. Remove extra spaces between words first, so you don’t accidentally insert extra symbols.

We’ll use the following Excel functions:

1. IF — to create a condition and give Excel instructions based on the result;
2. CONCATENATE (or just “&”) — to add the symbol before the cell value;
3. SUBSTITUTE — to replace spaces with a space plus the symbol;
4. TRIM — to remove extra spaces before replacing.

Here’s an example formula:

=IF(A1="","",D1&SUBSTITUTE(TRIM(A1)," "," "&D1))

Here, D1 is the cell containing the symbol you want to insert. You can also hardcode the symbol directly into the formula. For example, to insert an exclamation mark before each word:

=IF(A1="","","!"&SUBSTITUTE(TRIM(A1)," "," !"))

Symbol after every word in a cell – Excel formula

To insert a symbol after each word in a cell (for example, a comma or semicolon), use a similar formula with a small modification:

=IF(A1="","",SUBSTITUTE(TRIM(A1)," ",D1&" ")&D1)

Here, D1 contains the symbol. The formula will automatically add it after each word.

Insert symbols before and after each word in 2 clicks

If you don’t want to write or copy long formulas, the !SEMTools add-in can do it instantly. Just go to the “Change Words” menu in the “CHANGE” group and choose the option to insert symbols before or after each word in the selected cells.

In the example below, we wrap every word in quotation marks:

Conclusion

You’ve learned how to insert symbols before and after every word in a cell using standard Excel formulas, and how to do the same in seconds with the !SEMTools add-in. You might also be interested in other ways to change words in Excel:

• Capitalize every word in Excel
• Lemmatization

If you added unwanted symbols, see how to quickly remove all characters except letters and numbers.