In this Excel tutorial, you will learn about the carriage return in Excel. It includes what carriage returns are, and how to insert or remove them using different methods.
We used Microsoft 365 to prepare this article. But you can use the described methods in all versions of Excel. However, the TEXTJOIN function will only work on versions of Excel 2019 and onwards.
Carriage return means adding a line break in an Excel cell. Whenever we insert addresses, personal information, mail labels, or multiple tasks in a single cell, we need to use a line break. It improves the structure and readability of your data.
In the following image, we have used the CHAR(10) with other functions to add carriage returns. For example, in Cell E5, we used the ampersand (&) operator to join cells B5, C5, and D5. We also applied the CHAR(10) function to add carriage returns after cells B5 and C5.
Click the image to get a detailed view
⏷What is Carriage Return?
⏷Insert Carriage Return in Excel
⏵Use Keyboard Shortcuts
⏵Merge CHAR, CONCATENATE & TEXTJOIN Functions
⏵Use Find and Replace Feature
⏵Apply VBA Code
⏷Remove Carriage Return
⏵Use Find and Replace Feature
⏵Merge TRIM and SUBSTITUTE Functions
⏵Use VBA Code
⏷Tips for Carriage Return in Excel
⏷Things to Remember
⏷Frequently Asked Questions
⏷Carriage Return in Excel: Knowledge Hub
What Is a Carriage Return?
Carriage Return means pushing some of the texts of a cell to a new line of the same cell. Generally, we use it when we put information from different cells into one cell and show each piece of information in a separate line.
Carriage return helps to add a line break to a single cell instead of using multiple cells. It is also known as the return or line feed character. ASCII code 10 represents the character for Windows.
We can get a similar feature using the Wrap Text feature. However, it doesn’t always allow the line to break in the desired position. Adding a carriage return puts the line break where we want it to be.
How to Insert Carriage Return in Excel
1. Insert Carriage Return Using Keyboard Shortcuts
- Select the cell and press the F2 key to enter edit mode.
- Then, place the cursor where you want to add a carriage return and press Alt+Enter on your keyboard. For Mac, you need to use Control+Option+Return or Control+Command+Return.
- The carriage return or line break will occur at that place.
- Do this for every carriage return in the cell.
2. Using Excel CHAR, CONCATENATE & TEXTJOIN Functions to Insert Carriage Return
The character used for carriage return is different in Windows and Mac. For Windows, it is CHAR(10), and For Mac, it is CHAR(13). We can combine this character with other functions to get a carriage return.
In this example, we will show the formulas for Windows. You need to replace CHAR(10) with CHAR(13) for Mac.
- You can use the function with ampersand to produce a string with the carriage return.
=B5&" "&CHAR(10)&C5&" "&CHAR(10)&D5- Or, you can use the CONCATENATE function instead.
=CONCATENATE(B6,CHAR(10),C6,CHAR(10),D6)- You can also use the TEXTJOIN function to join the texts with a carriage return in between. This function is available in Excel 365, Excel 2021, and Excel 2019 versions.
=TEXTJOIN(CHAR(10),TRUE,B7:D7)Click the image to get a detailed view
3. Using the Find and Replace Feature to Insert Carriage Return
If you have a worksheet with multiple one-line entries, you may need to break lines to use the data easily. It can take a lot of time if you do this manually. In that case, you can use the Find and Replace feature.
In this method, we have some data in cells separated by commas. We will add a carriage return in place of a comma.
- Press Ctrl+H to open the Find and Replace dialog box.
- Then select the character you want to replace a carriage return with. We are replacing “, “, so we put that in the Find what field.
- In the Replace with field, press Ctrl+J. It will insert the carriage return in that field.
Click the image to get a detailed view
- After that, click on Replace All and resize the cells to get your desired view.
4. Using Carriage Return in VBA
- Go to the Developer tab and select Visual Basic from the Code group.
- Now select Insert and then Module in the VBA window.
- Then insert the following code in the module.
Sub CRinMacro()
MsgBox "First Line" & vbCrLf & "Second Line"
End Sub
- Press F5 to run the code. You will get a carriage return where vbCrLf is used.
How to Remove Carriage Return in Excel
There are different reasons for carriage returns occurring in Excel. Carriage returns may appear when you copy texts from a website or use downloaded CSV or Text files in Excel. To organize the data, you can remove carriage returns following three methods.
1. Remove Carriage Return Using Find and Replace Feature
- Open the Find and Replace dialog box by pressing Ctrl+H.
- Then press Ctrl+J to insert the carriage return in the Find what field.
- Insert the delimiter you want to replace the carriage return with to win the Replace with field.
Click the image to get a detailed view
- Select Replace All after that and it will remove all the carriage returns on the spreadsheet.
2. Using TRIM and SUBSTITUTE Functions to Remove Carriage Return
In this method, we will remove carriage return using some functions. In the dataset below, we have some personal information in Cell B5 and B6. After removing the carriage returns, the results will be like Cell C5 and C6 respectively.
- You can combine the TRIM and SUBSTITUTE functions to remove carriage return from a cell. You can use a space in place of the comma.
=TRIM(SUBSTITUTE(SUBSTITUTE(B5,CHAR(13),""),CHAR(10),", "))- Or you can use only the SUBSTITUTE function to replace the carriage return with another character in Excel.
=SUBSTITUTE(B6,CHAR(10),", ")
Click the image to get a detailed view
=CLEAN(B5).3. Remove Carriage Return Using VBA in Excel
You can use a VBA code to remove carriage returns quickly in Excel. In this method, you need to save the code in a module, select the range that contains carriage returns, and run the code.
For example, we have some information in Cell B5 and B6 with carriage returns. We will replace the carriage returns with a comma followed by a space. In the following image, you can see the before and after of using the VBA code.
- To apply the VBA, go to the Developer tab >> select Visual Basic.
- From the Visual Basic window, select Insert >> Module.
- Paste the following code in the Module window.
Sub Delete_Carriage_Returns()
Dim iRange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each iRange In ActiveSheet.UsedRange
If 0 < InStr(iRange, Chr(10)) Then
iRange = Replace(iRange, Chr(10), ", ")
'the carriage returns will be replaced by a comma along with a space
End If
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Click on the image to get a detailed view
- Now, select the range >> Macros >> from the Macro dialog box >> select the code >> Run.
Click on the image to get a detailed view
- You will get results like the image below.
Tips for Carriage Return in Excel
- If the row height isn’t set to auto-adjust with the text, the cells with carriage returns will not display the whole text. Use the Wrap Text feature from the Home tab to display the whole text.
- You can add multiple carriage returns to increase readability.
- When your formula gets relatively large, you might want to add carriage returns in formulas. This helps users understand them better. It won’t affect the formula’s functionality.
Things to Remember
- By default, pressing Enter will take you to the cell below in Excel. Use Alt+Enter if you want to apply the carriage return directly in the Excel cell.
- Use text wrapping if your cell values with carriage returns aren’t displaying fully.
- The Find and Replace feature or the SUBSTITUTE function can replace carriage returns with a specific character at a time and vice versa.
- The TRIM function removes all the spaces from a text. Use that with caution, as it can remove the necessary ones too.
- When transferring or exporting data with carriage returns from Excel to other applications, some may treat them as other characters. This happens because different applications treat it as different characters.
Frequently Asked Questions
1. How to add carriage return in Excel for multiple cells?
Answer: You can use formulas and the Find and Replace features to add carriage returns for multiple cells. Using the latter, you can also add them over the entire sheet.
2. How do I adjust the row height to display the entire text with carriage returns?
Answer: To adjust the rows to the texts, double-click below the row number on the left of the spreadsheet. It will automatically adjust with the number of lines created by carriage returns.
3. Can I export or print Excel data with carriage returns?
Answer: Yes, you can export or print Excel data with carriage returns in some applications. However, some applications may have different character codes for the carriage return. So you have to consider the target file format and printer settings in such scenarios.
Download Practice Workbook
Conclusion
That concludes our discussion on the carriage return in Excel. It includes how to insert and remove carriage returns and some useful tips for using it in Excel. Be sure to check out the knowledge hub for more in-depth guides about different topics on the carriage return. I hope you found the guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
