Excel Insert Line Feed to Cell
In Excel, you might come across situations where you need to combine strings into a single cell, but you want to display them in separate lines.
For example, you might have different address components like House number, Street name, city, state, and country, and you might want to combine them into a single cell containing the full address, where the main address, state, and country are in separate lines.
If it were a word processing software, simply pressing the return key would result in a line break. However, in spreadsheet software like Excel, it's not that simple.
In this tutorial we will show you three ways to combine strings in Excel with line breaks:
- Using the Ampersand Operator (&)
- Using the CONCATENATE Function
- Using the TEXTJOIN Function
Before we show you how to combine text with line breaks, it is important to understand how line breaks are represented in Excel.
In regular programming languages, the '\n' character is used to represent line breaks. However, in Excel, we need to use a Unicode character, specified using the CHAR function.
The CHAR Function
The CHAR function in Excel returns the character that corresponds to an ASCII value. It is usually used to specify characters in Excel that are hard to enter into a formula.
An ASCII value is an integer between 1 and 255, each one representing a specific character. For example, the ASCII code for the line break character is 10.
The CHAR function takes just one parameter, which is the ASCII code that you want to convert to a symbol. So to display a line break character in Excel, we use the formula =CHAR(10).
We will be using this function in all the formula methods of this tutorial to create a line break character.
3 Ways to Concatenate with Line Breaks in Excel
Now let us look at the three ways to use this CHAR function and concatenate with line breaks in Excel.
To demonstrate all three methods, we will be using the following dataset:
We will concatenate the Street address, City, and State for each row into a single cell of column D, separating them with line breaks.
Method 1 – Using the Ampersand (&) Operator
This method is useful if you have a small number of strings that you want to concatenate into a single cell.
The ampersand operator (&) lets you join items (or strings) without having to use a function.
The operator simply takes the string to its right and joins it to the end of the string that is specified to its left. For example, the formula ="Micro"&"Soft" returns the string "Microsoft".
In our example, we can use the ampersand operator as follows to get the result of row 2 in column D:
=A2&CHAR(10)&B2& CHAR(10)&C2
Then copy the formula down to the rest of the cells of column D. The result we will get is as follows:
Formatting Results with Wrap Text
Notice from the above result, that we were able to combine all the three cell values alright, but they still don't seem to be separated with line breaks.
They all appear in the same line!
The line breaks are there alright, but we just don't see them because we haven't formatted the resulting cell to display them.
To display your line breaks, you need to format the resultant cells in the 'Wrap text' format.
So select your cells in column D, press CTRL+1 to open the Format Cells dialog box, and check the box next to Wrap text (under the Alignment tab). Then click OK.
You should now see your cells display the address components in separate lines:
Method 2 – Using the CONCATENATE Function
The next method uses the CONCATENATE function to basically do the same thing.
The CONCATENATE function is one of the text functions of Excel, that essentially joins two or more text strings into a single string.
The syntax for this function is as follows:
CONCATENATE(text1, [text2], ...)
Where each parameter (text1, text2, etc.) are string values that you want to combine. You can combine any number of strings.
The first parameter is required, while any parameters after that are optional. In the absence of any more parameters, the function simply returns the string passed to it.
In our example, we can use the CONCATENATE function as follows to get the result of row 2 in column D:
=CONCATENATE(A2,CHAR(10),B2, CHAR(10),C2)
Then copy the formula down to the rest of the cells of column D. The result we will get is as follows:
Note: You will need to format your results to Wrap text in order to get the address components to appear in separate lines.
You can also use the CONCAT function instead of CONCATENATE. Both functions do more or less the same thing.
The only difference is that the CONCAT function lets you reference a range of cells instead of individual ones.
However, in this case, we only need to reference the cells individually (since each string needs to be separated by a line break character).
So, it doesn't really matter which of the two functions you use.
Method 3 – Using the TEXTJOIN Function to Concatenate (for Excel 2019 and Office 365 only)
This method is by far the easiest and quickest and is ideal if you have a lot of components that you want to concatenate
The TEXTJOIN function is a newly added Excel function, which is only available in newer versions like Office 365, Excel 2019, and higher.
This function combines strings from multiple ranges with a specified delimiter between each text value.
A delimiter is a character or set of characters that are used to separate strings. For example, a common delimiter to separate text is the space character.
Other delimiters could be the comma, semi-colon, etc. In our case, our delimiter is the line break character, since we want to combine the address components and then separate them with line breaks.
The syntax for the TEXTJOIN function is as follows:
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
Here,
- delimiter is the character or string you want to specify as a delimiter to separate the concatenated strings. This parameter can also be empty. The delimiter needs to be specified between double quotes.
- The ignore_empty parameter is used to specify if we want empty values to be included in the combined string. A TRUE value indicates that we want empty values ignored, while a FALSE value indicates that we want empty values included in the result.
- text1, text2, … are the strings or ranges of cells containing strings that we want to combine.
In our example, our delimiter is the line break character, or CHAR(10). So, we can use the TEXTJOIN function as follows to get the result of row 2 in column D:
=TEXTJOIN(CHAR(10),TRUE,A2:C2)
Then copy the formula down to the rest of the cells of column D. The result we will get is as follows:
Note: You will need to format your results to Wrap text in order to get the address components to appear in separate lines.
As you can see, using the TEXTJOIN function, we could reference all the strings in one range, instead of having to separately refer to individual cells like in the previous two methods.
Moreover, we had to define the delimiter just once in the formula instead of repeating it after every string.
In this tutorial, we showed you three different ways to concatenate with line breaks in Excel.
The first two methods are more or less the same, with the exception that the first one uses an operator while the second one uses a function.
The third method, on the other hand, is much quicker and simpler.
However, the function used in this method (the TEXTJOIN function) is only available in newer Excel versions like Office 365, Excel 2019, and higher.
I hope this short tutorial was helpful.
Other Excel tutorials you may also find helpful:
- How to Remove Dotted Lines in Excel
- How to Extract Text After Space Character in Excel?
- How to Add Text to the Beginning or End of all Cells in Excel
- How to Separate Address in Excel?
- How to Remove a Specific Character from a String in Excel
- How to Break Links To External References in Excel?
Source: https://spreadsheetplanet.com/concatenate-with-line-breaks-excel/
0 Response to "Excel Insert Line Feed to Cell"
Post a Comment