How can I add a character before each word in a cell using Excel?

By /

The process of adding a character before each word in a cell using Excel involves using a formula or function to concatenate the desired character with the existing text in the cell. This allows for the insertion of a specific character, such as a symbol or letter, at the beginning of each word in the cell. This can be useful for various purposes, such as creating a list or adding formatting to the text. By following the correct steps, this task can be easily achieved in Excel, providing a quick and efficient way to modify cell data.

Excel: Add a Character Before Each Word in Cell

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: Add a Character Before Each Word in Cell
  2. Google Sheets: Extract Text Before a Character
  3. How can I assign a value in Excel based on whether a cell contains a specific word?
  4. How can I extract the first word from a cell in excel?
  5. How to Extract Last Word from Cell in Excel?
  6. how to assign a value in excel if cell contains a word
  7. How can I extract all but the first character from a cell in Excel?
  8. Another Cell How can I use conditional formatting in Google Sheets to highlight a cell if it is equal to or greater than another cell?
  9. How to Use LEFT to Extract Text Before Comma in Excel?
  10. How do I extract the first word from a cell in Google Sheets?