How to Add a Character or Letter before Each Word in a Cell


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.

So How to add a character before each word in a cell? You can use a combination of the concatenate operator and the SUSTITUTE function to create an excel formula as follows:

="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, " "," #")

add character before each word1

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.

Related Functions

  • 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])….

Leave a Reply