How to Remove CHAR(10) blank from CONCATENATE formula

This guide will explain how to remove blank lines when concatenating values.

In some cases, you may want to combine values from multiple cells into a single cell with multiple lines. For example, you may have a range containing the street address, city, and zip code and would like to combine these values into one cell.

One way to do this is through the CONCATENATE formula and a newline character. However, one limitation when using this method is that the CONCATENATE formula does not automatically manage empty cells in the range.

In this guide, we will provide a step-by-step tutorial on how to use the TEXTJOIN function instead of concatenating multiple cells with a line break. We’ll provide a detailed explanation of how you can set up a formula that can handle ranges with missing data.

The Anatomy of the TEXTJOIN Function

Before looking at an example, let’s take a look at how the TEXTJOIN function works.

The syntax of the TEXTJOIN function is as follows:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Let’s look at each argument to understand how to use the TEXTJOIN function.

• = the equal sign is how we start any function in Google Sheets.
• TEXTJOIN() refers to our TEXTJOIN function. This function allows us to combine text from multiple ranges with a specified delimiter to be placed between each value. 
• delimiter refers to a specific text string or reference to a valid text string that you would like to use as a delimiter.
• The ignore_empty  parameter allows us to specify what the function will do when it encounters empty cells. If set to TRUE, TEXTJOIN will ignore empty cells.
• text1 refers to the text item we want to be joined with other cells. The argument can be a single text string or an array of strings.
• [text2, …] refers to optional text items that you want to join with the TEXTJOIN function.  The argument can be a single text string or an array of strings.

A Real Example of Concatenating Values While Removing Blank Cells

Let’s explore a simple example where we can concatenate all non-blank values into a single cell.

In the table above, we have a table that holds up to five numerical values. Notice that the third value is left blank.

Suppose you want to combine the values in the range B1:B5. You want the output to have each cell value separated by a line break. This will enable each value to be displayed in a separate line.

Let’s use the following function to try this out:

=CONCATENATE(B1,CHAR(10),B2,CHAR(10),B3,CHAR(10),B4,CHAR(10),B5)

Using this formula, we’re able to use the line break character by using CHAR(10) as an additional argument between each cell value. However, our output includes a blank space since cell B3 is blank.

To avoid blank spaces, we’ll need to use the TEXTJOIN function instead. We can use the following updated formula to avoid empty lines in our output:

=TEXTJOIN(CHAR(10), TRUE, B1:B5)

In the example above, we can see that the output no longer contains an empty line. This is because the TEXTJOIN function’s ignore_empty argument has been set to TRUE.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the TEXTJOIN function to remove blanks from concatenated strings in Excel.

How to Remove CHAR(10) blank from CONCATENATE formula

1. Select an empty cell and type “=TEXTJOIN(“ to start the TEXTJOIN function.
   
2. The first argument of the TEXTJOIN function specifies the delimiter to use when joining text. Since we want each text value to appear in separate lines, we’ll need to use the newline character.
   The easiest way to include the newline character is to enter CHAR(10) as an argument. The CHAR function allows us to return a specific character given a valid character code.
   
3. Next, add a comma and enter TRUE for the ignore_empty argument.
   When we set this argument to TRUE, the TEXTJOIN function will skip over cells with no text.
   
4. Next, type another comma and provide the range of cells you would like to join into a single string.
   In this example, we’ll use the range B1:B5.
   
5. Hit the Enter key to evaluate the TEXTJOIN function.
   

These are all the steps you need to know to concatenate text strings without leaving blank lines.

To learn more about using Excel for concatenating strings, you can read our post on how to stack multiple columns into a single column.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!