Open In App

How to Concatenate with a Line Break in Excel?

Improve
Improve
Like Article
Like
Save
Share
Report

Excel is a powerful tool to store, manage and visualize large amounts of data. This tool is usually used by accounting professionals for the analysis of financial data but can be used by anyone to manage large data.  Data is entered into the rows and columns. And then, various formulas and functions can be used to get insights from the data.

Using Concatenate with a Line Break in Excel

Concatenation refers to joining one or more strings. The line break is added to end the current line and start a new line within the same cell. 

Line-break

 

Ways to perform the task

  1.  Using Concatenate function
  2.  Using Ampersand (&) sign

Method 1: Using Concatenate function

Concatenation refers to joining one or more string. CONCATENATE() is the function that join two or more text string into one string. It takes the value to join as an argument.

Syntax: CONCATENATE(text1, text2, text3……textn)

For purpose of demonstration take the below table and concatenate the values in columns A, B, and C with a line break.

Dataset

 

Step 1: Use CONCATENATE() function

Using-concatenate-function

 

Double quotation with a space in between (” ”) will add a space between the texts.

Step 2: Drag the lower right corner downwards to apply the formula to the rest of the cells.

Applying-formula

 

Step 3: Go to Home > Alignment > Wrap Text.

Wrap-text

 

Output

Output

 

Method 2: Using Ampersand (&) sign

Ampersand sign is a concatenation operator. It is used in between the text to be joined. CHAR(10) in between the formula can be used to enter a line break.

Syntax: text1 & CHAR(10) & text2 & CHAR(10) & text3…

Example

Applying-function

 

Output

Output

 


Last Updated : 24 Jun, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads