How to use the SUBSTITUTE function to remove word wrapping in cells in Excel

Sometimes, when you copy and paste text from another source into Excel, you may encounter unwanted line breaks or word wrapping in the cells. This can make your data look messy and hard to read. One way to fix this problem is to use the SUBSTITUTE function in Excel. The SUBSTITUTE function replaces one or more instances of a specific text with another text in a given cell or range of cells. In this article, we will show you how to use the SUBSTITUTE function to remove word wrapping in cells in Excel.

The SUBSTITUTE function has the following syntax:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

The arguments are:

• text: The cell or range of cells that contains the text you want to modify.
• old_text: The text you want to replace.
• new_text: The text you want to use instead of the old text.
• instance_num: (Optional) The number of the occurrence of the old text that you want to replace. If omitted, all occurrences of the old text will be replaced.

The SUBSTITUTE function returns a new text value that has the specified replacements.

Procedures

To use the SUBSTITUTE function to remove word wrapping in cells in Excel, follow these steps:

1. Identify the character that causes the line break or word wrapping in your text. Usually, this is a carriage return character (CHAR(10)) or a line feed character (CHAR(13)). You can use the CODE function to find out the numeric code of the character in your text. For example, if your text is in cell A1, you can use =CODE(RIGHT(A1,1)) to get the code of the last character in the cell.
2. Use the SUBSTITUTE function to replace the line break or word wrap character with a space or an empty string in your text. For example, if your text is in cell A1 and the line break character is CHAR(10), you can use =SUBSTITUTE(A1,CHAR(10)," ") to replace it with a space, or =SUBSTITUTE(A1,CHAR(10),"") to remove it completely. You can also use the instance_num argument to specify which occurrence of the character you want to replace, if you don’t want to replace all of them.
3. Copy the result of the SUBSTITUTE function and paste it as values in another cell or range of cells. This will remove the formulas and keep only the modified text.

Example

Let’s say you have the following text in cell A1, which has unwanted line breaks:

This is a
sample text
with line breaks

You can use the SUBSTITUTE function to remove the line breaks and get the following result in cell B1:

This is a sample text with line breaks

Here is the formula you can use in cell B1:

=SUBSTITUTE(A1,CHAR(10)," ")

Here is a table that shows the original text, the code of the line break character, and the modified text:

Other approaches

Besides using the SUBSTITUTE function, there are other ways to remove word wrapping in cells in Excel. Here are some of them:

• Use the CLEAN function to remove all non-printable characters from your text, including line breaks. For example, =CLEAN(A1) will remove any line breaks from cell A1.
• Use the TRIM function to remove any extra spaces from your text, including those caused by line breaks. For example, =TRIM(A1) will remove any leading, trailing, or repeated spaces from cell A1.
• Use the Find and Replace feature to replace the line break or word wrap character with a space or an empty string. To do this, select the cell or range of cells that contains the text, press Ctrl+H to open the Find and Replace dialog box, enter the line break or word wrap character in the Find what box, enter a space or an empty string in the Replace with box, and click Replace All.