Table of Contents
Excel is a powerful tool to help users organize and manage data. It is especially helpful in dealing with large datasets, and in manipulating data quickly and efficiently. One of the useful functions of Excel is the ability to add a character before each word in a cell. This function is useful for adding formatting or highlighting certain words, making data easier to read and interpret. In this article, we will discuss the importance of this Excel function and explain how to use it.
You can use the following formula to add a specific character before each word in a cell in Excel:
="_"&SUBSTITUTE(A2," "," _")
This particular formula adds an underscore _ before each word in cell A2.
For example, if cell A2 contained Andy Miller then this formula would return _Andy _Miller.
The following example shows how to use this formula in practice.
Example: Add a Character Before Each Word in Excel
Suppose we have the following column of names in Excel:
Suppose we would like to add an underscore _ before each word in each cell.
We can type the following formula into cell B2 to do so:
="_"&SUBSTITUTE(A2," "," _")
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains the text in column A with an underscore added before each word.
Note that you could also add several characters before each word if you’d like.
For example, we could type the following formula into cell B2 to add “text” before each word in cell A2:
="text"&SUBSTITUTE(A2," "," text")
Column B now contains the text in column A with “text” added before each word.
How This Formula Works
Recall the formula that we used to add an underscore before each word in cell A2:
="_"&SUBSTITUTE(A2," "," _")
Here is how this formula works:
First, we type “_” to add an underscore before the first word in the cell.
Then we use the SUBSTITUTE function to substitute each occurrence of a space in cell A2 with a space and an underscore.
The end result is that we’re able to add an underscore before each word in cell A2.