One of the most widely used applications in our day-to-day lives is the spreadsheet application Microsoft Excel. Excel was created solely with the intention of increasing user productivity. It works as expected most of the time, but in some cases, it may actually be to your disadvantage. You have come to the right place if you are looking for some unique solutions to the issue with the CHAR(10) function not working in Excel. This issue can be resolved in a number of different ways in Microsoft Excel. The issue will be addressed in three different ways in this article. Let’s follow the entire manual to learn everything.
CHAR(10) Is Not Working in Excel: 3 Solutions
In the following section, we will use three effective and tricky solutions to fix the problem with the CHAR(10) function not working in Excel. This section provides extensive details on these solutions. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
Solution 1: Use Wrap Text Command
Here, we’ll show you how to fix the problem with CHAR(10) not working in Excel. Excel occasionally fails to insert a line break when we attempt to use the CHAR(10).
- First of all, we want to insert a line between each word by using the following formula.
=B5&CHAR(10)&C5&CHAR(10)&D5- Next, press Enter.
- As a result, you will notice that there is no line break between words, indicating that the CHAR(10) function is not functioning properly.
- If you drag the Fill Handle icon to fill the other cells with the same formula, you will get the following output where Excel CHAR(10) is not working.
To solve the problem, we’ll get the help of the Excel Wrap Text command. It’s simple & easy, just follow the following process.
📌 Steps:
- Select the range of the cells and go to the Home tab.
- Then, select the Wrap Text command.
- Now, you have to select the range C5:C9.
- Then, go to the Home tab, and select Format. Finally, select AutoFit Row Height.
- You will be able to solve the problem and get a line break in an Excel cell as shown below.
Read More: How to Use CHAR(10) Function in Excel
Solution 2: Combine CONCATENATE with CHAR Function
Here, we will demonstrate another way to fix the problem. Here, we are going to combine CONCATENATE and CHAR functions to add new lines between words in Excel. Let’s take a step-by-step look at the matter with proper illustrations when Excel fails to insert a line break if we attempt to use the CHAR(10) function.
- First of all, we want to insert a line between each word by using the following formula.
=CONCATENATE(B5,CHAR(10),C5,CHAR(10),D5)- Next, press Enter.
- Therefore, you will notice that there is no line break between words, indicating that the CHAR(10) is not functioning properly.
- Next, if you drag the Fill Handle icon to fill the other cells with the same formula, you will get the following output.
To solve the problem, you will have to follow the following process.
📌 Steps:
- Select the range of the cells and go to the Home tab.
- Then, select the Wrap Text command.
- Now, you have to select the range C5:C9. Then, go to the Home tab, select Format, and finally, select AutoFit Row Height.
- You will be able to solve the problem and get a line break in an Excel cell as shown below when Excel CHAR(10) is working properly.
Read More: Character Codes for CHAR Function in Excel
Solution 3: Use TEXTJOIN with CHAR Function
We will illustrate yet another approach to fix the problem with the CHAR(10) not working in Excel in this section. To add new lines between words in Excel, we are going to combine the TEXTJOIN and CHAR functions here. Let’s take a look at the situation one step at a time with appropriate examples of when Excel fails to insert a line break when we use the CHAR(10) function.
- We want to add a line between each word by using the following formula.
=TEXTJOIN(CHAR(10),TRUE,B5,C5,D5)- Next, press Enter.
- Therefore, you will notice that there is no line break between words, indicating that the CHAR(10) function is not functioning properly.
- Next, if you drag the Fill Handle icon to fill the other cells with the same formula, you will get the following output.
To solve the problem, you will have to follow the following process.
📌 Steps:
- Select the range of the cells and go to the Home tab.
- Then, select the Wrap Text command.
- Now, you have to select the range C5:C9. Then, go to the Home tab, select Format, and finally, select AutoFit Row Height.
- Eventually, you will be able to solve the problem and get a line break in an Excel cell as shown below when the Excel CHAR(10) function is working properly.
Read More: How to Convert Excel ASCII to Char
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods in different spreadsheets for a clear understanding.
Conclusion
That’s the end of today’s session. I strongly believe that from now on, you may fix the problem with the CHAR(10) function not working in Excel. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website for various Excel-related problems and solutions. Keep learning new methods and keep growing!
