Insert a new line in cells (Using a code)

To insert a new line in Excel, we can add a text called ‘line feed code = CHAR (10)’. Let us review this line feed code first. → For more information on the CHAR function, click here

 

Example of use) Insert a new line using the line feed code

Below is a list that shows the address of employees in a company. The result of combining information in column C and D and inserting a new line in between is displayed in column E.

① Combine information in column C and D using the line feed code.

(1) Add “CHAR(10)” when combining cell C4 and D4 with the symbol ‘&’.
=C4& CHAR(10) &D4

If we include the code in between cell C4 and D4, a new line will be inserted after the information in column C, and the information in column D will appear on the 2nd line.

 

Replace the line feed code

There is an easier way to insert a new line in Excel, which is to push ‘Alt+Enter’. This is a more popular method to insert a new line, but there is an advantage in using the line feed code.
That is, we can easily replace the code with something else, or delete it completely within the formula. This might sound confusing so let us look at an example.

 

Example of use) Replace the line feed code

Below is an example of replacing the code with a different text.

 

(1) Click [Home] → ‘Find & Select’ → ‘Replace’.

 

(2) Follow the steps below:

① Input text we wish to replace in [Find what:], ‘CHAR(10)’.
② Input text we wish to replace ① with using ” (double quotations) in [Replace with:]. → ” Address:”
③ Click [Replace All] to complete.

 

(3) With Replace, the ‘line feed code’ has been replaced with the text ‘Address:’. (※We included a space before “Address:” in ②, because we wished to have a space between the postal code and “Address:”)

 

Delete the line feed code

To delete all the new lines created with the line feed code, follow the example below.

 

Example of use) Delete the line feed

(1) Change the following settings:

① Input text we wish to replace in [Find what:], ‘CHAR(10)’.
② In this case we want to delete the content in ①, so leave [Replace with:] blank.
③ Click [Replace All] and complete.

 

List of CHAR functions (tabs, operators, commas…)

Lastly, we will look at other examples of texts and symbols that we can use with the CHAR function.

 

Example of use) Include a Tab

Normally in Excel, the TAB serves as a key to ‘move to the right cell’, so if we we select cell B2 and click on the TAB key, cell C2 will be selected.
%e3%82%bf%e3%83%963

This operation itself is useful in many ways, but it cannot serve its common purpose of ‘inserting a space in between the words’ or ‘indenting’.
The CHAR function is useful in this occasion.

 

Below in column E, we added a space with TAB using the CHAR function.

The TAB in the CHAR function is symbolized as ‘CHAR(9)’, so the formula would be the following:

=C4&CHAR(9)&D4

 

Now we have successfully added a TAB space in between the information in C4 and D4. However, it is difficult to see the space in Excel.

To make sure there is a TAB space, let us save it in CSV format and check the content in the Notepad.

 

When the CSV file is opened in Notepad)

The part surrounded by the red line is the part we added a TAB space. We can see clearly that there is a space in between the postal code and the rest of the address.

 

Below is a list of other symbols that we can use with the CHAR function:

Symbols Displayed in the CHAR function as
! CHAR(33)
CHAR(34)
$ CHAR(36)
& CHAR(38)
CHAR(39)
< CHAR(60)
= CHAR(61)
> CHAR(62)
@ CHAR(64)