askvity

How do I remove line breaks in Sheets?

Published in Google Sheets 3 mins read

To remove line breaks in Google Sheets, you can use the SUBSTITUTE function along with the CHAR function. This combination allows you to replace the line break character (CHAR(10)) with a space or any other character you prefer.

Here's a step-by-step guide:

  1. Select the cell or range of cells that contain the text with line breaks you want to remove.

  2. Enter the following formula into an empty cell: =SUBSTITUTE(A1, CHAR(10), " ")

    • Replace A1 with the cell containing the text you want to modify.
    • CHAR(10) represents the line break character.
    • " " represents a single space. You can change this to "" to remove the line breaks entirely (resulting in text without spaces between the previously separated lines) or replace it with any other character or string.

  3. Press Enter or Return to apply the formula. The cell containing the formula will now display the text from the original cell, but with the line breaks replaced by spaces.

  4. Copy and paste values (optional): If you want to replace the original text with the modified text, you can copy the cell containing the formula and then paste it as values back into the original cell(s). To do this:

    • Select the cell containing the formula result.
    • Copy the cell (Ctrl+C or Cmd+C).
    • Select the original cell(s) containing the line breaks.
    • Right-click and choose "Paste special" -> "Paste values only." This will replace the formula with the resulting text.

Example:

Let's say cell A1 contains the following text:

This is line 1. This is line 2. This is line 3.

If you enter the formula =SUBSTITUTE(A1, CHAR(10), " ") into cell B1, cell B1 will display:

This is line 1. This is line 2. This is line 3.

If you use =SUBSTITUTE(A1, CHAR(10), "") into cell B1, cell B1 will display:

This is line 1.This is line 2.This is line 3.

Explanation:

  • The SUBSTITUTE function replaces specific characters in a text string with other characters.
  • The CHAR(10) function returns the character associated with ASCII code 10, which is the line feed character (a common type of line break).
  • By replacing CHAR(10) with " ", you are effectively replacing the line breaks with spaces. Replacing with "" replaces with nothing.

Related Articles