This post will guide you how to add a specified character or letter before each word in a cell in excel. How to add a character to each word in a cell using excel formula. How to add a hash character before each word with a formula.
Add a character or letter before each word
In the previous posts, we talked that how to add the same text or characters into the beginning of the first word for all cells. And How to add text or characters to the end of all cells in excel.
="Character"&SUBSTITUTE(Cell, " "," Character")
Assuming that you would like to add a hash “#” sign before each word within a bunch of cells, such as: Cell B1, then you can write down the following formula based on the above generic formula:
="#"&SUBSTITUTE(B1, " "," #")
The concatenate operator will join the hash sign before the first word in a cell. And the SUBSTITUTE function will replace all space characters with new character hash sign. So this formula will add the hash character before each word in Cell B1.
Note: if the text strings in one cell are separated by other specific character, such as: hash, comma, etc. and then you need to update the second argument in the SUBSTITUTE function from space character to another character.
- Excel Substitute function
The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE (text, old_text, new_text,[instance_num])….