How to Substitute Text for Excel Replacement Formulas

When importing data into Excel from various sources (databases, tables from web pages, etc.), unwanted extra spaces in cell values often accompany the data. Some may be at the beginning of the text, while others are at the end.



How to Remove Extra Spaces in an Excel Cell?

Dynamic Word Selection from String

Unnecessary spaces can distort information and adversely affect formula operations. Problems may arise when using search functions, creating charts, adjusting column widths, or printing sheets.

The example image below illustrates how to remove extra spaces and indents using the TRIM function:

TRIM Function

The TRIM function is straightforward. Just specify the cell reference with the original text in its argument, and the function will automatically remove all spaces and indents, except single spaces between words in the text string.

However, due to its simplicity, the TRIM function has limitations. It's advisable to use it in combination with other text functions to handle more complex tasks of removing extra spaces and indents in text.

The TRIM function is designed to remove only extra spaces of the 32nd ASCII character code. However, Unicode may have other space or tab characters, such as the non-breaking space with a code of 160 in the Unicode character table.

Failed to Remove Spaces

To remove extra spaces in a text string containing a non-breaking space with code 160, use a formula that combines three functions:

  1. TRIM - removes only extra spaces in the text string, excluding spaces between words.
  2. SUBSTITUTE - finds text characters with a code of 160 and replaces them all with a space with a code of 32.
  3. CHAR - enters any text character based on the code number specified in the function argument.

All three tasks can be accomplished in one formula:

=SUBSTITUTE(A2, CHAR(160), "")
Example Ready

We simply found and replaced extra characters with those that the TRIM function can handle, and it successfully completed the task.

Useful Tip! If you need to remove all spaces, use the SUBSTITUTE function instead of TRIM, as shown in the image:

SUBSTITUTE Function

The next formula selectively removes spaces between words, letters, and symbols:

=CHAR(34) & TRIM(SUBSTITUTE(A4, CHAR(34), " ")) & CHAR(34)
TRIM and SUBSTITUTE Formula

This way, you can remove not only all spaces but also any other characters that need to be cleared from the original text.

Practical Example of the SUBSTITUTE Formula

The application of formulas with the SUBSTITUTE text function is highly sought after in various MS Excel user workflows. One popular example is converting regular text into HTML format. Here are dynamic formulas that, when combined, allow you to choose a word to be highlighted with the <strong></strong> tag in a sentence. The formula in cell B5 implements the solution:

=SUBSTITUTE(B3, "!" & A6, "<strong>" & UPPER(A6) & "</strong>")

The formula's execution is possible after preprocessing the original text to insert an exclamation mark "!" at the beginning of the chosen word (as a marker). This is the task for the formula in cell B3:

=SUBSTITUTE(B2, LEFT(B2, A4), LEFT(B2, A4) & "!")
Insert HTML Strong Tags

Then, all that's left is to specify the ordinal number of the word in the row using a number in cell A9. As a result, the selected word will have opening and closing HTML <strong> tags for text highlighting:

Dynamic Word Selection from String

download file Download

All formulas can be combined into one complex combination, but to understand all the steps of the algorithm, it's better to break them down into separate working elements.