How to Replace Text with Carriage Return in Excel (4 Smooth Approaches)

For the fabrication of information, We can use Carriage Return. It means to return the beginning of the next line in the same cell. Here, We are going to discuss how to replace text with a carriage return in Excel.
We are going to use a Dataset containing Name and Information columns for clarification.

How to Replace Text with Carriage Return in Excel: 4 Smooth Approaches

1. Adopting Find & Select Options to Replace Text with Carriage Return

We can easily replace text with carriage return by adopting Find & Select options.

Steps:

• Select all the cells. I selected cells B5:C9.

• Then, Home —> Find & Select —> Replace.

A Find & Replace box will appear.

• Write the Text you want to replace in Find what. Here, I wrote Manchester United to replace.
• Press CTRL + J in Replace with as we want carriage return.
• Next, click on Replace all.

Then, we can have our desired output.

We can further decorate our worksheet by expanding the row heights.

Read More: How to Replace Text in Selected Cells in Excel

2. Using Keyboard Shortcuts to Replace Text with Carriage Return

We can also replace text with carriage return by using Keyboard Shortcuts.

Steps:

• First of all, select all the cells. I selected here cells B5:C9.

• Press CTRL + H to have Find & Replace box.
• Input the Text to be replaced in Find what. Here, I wrote Manchester United to replace.
• Next, press CTRL + J in Replace with as we want carriage return.
• Finally, Click on Replace all.

We will have our results on a screen.

We can further expand the row heights of our worksheet.

Read More: How to Replace Text with Blank Cell in Excel

3. Applying Substitute Function to Replace Text with Carriage Return

The SUBSTITUTE Function along with the CHAR function is another cool way to replace text with a carriage return.

Steps:

•  Create a separate column for output. I created a column named Output.
• Select a cell to apply the function. Here, I selected cell D5.

• Input the following formula:

Here, I used the SUBSTITUTE function to Replace the text Manchester United in the C5 cell with a carriage return by using the CHAR function.

• Now, select cell D5 then click on Wrap Text.

Then, we will have the text replaced with a carriage return.

• Use Fill Handle to AutoFill the rests.

You can modify the Row Heights according to your choice.

Read More: Excel Formula to Replace Text with Number

4. Using VBA to Replace Text with Carriage Return

Visual Basic for Applications(VBA) can also be a great option to replace text with a carriage return.

Steps:

• Select all the cells. Here, I selected cells B5:C9.
• Choose Visual Basic from the Developer Menu.

• Create a Module. For this, Select Insert and choose Module.

• Write the following Code:

Sub ReplaceText() 
Dim man_u As Range 
For Each man_u In Selection 
    man_u.Value = Replace(man_u, "Manchester United", vbLf) Next 
End Sub

Here, I used the Sub_procedure ReplaceText. I used Name.Value Property where I replaced the text Manchester United with a carriage return.

• Click on the Run button or press F5.

Finally, We will have the output.

Practice Section

For more expertise, you can practice here.

Download Practice Workbook

Conclusion

We have tried to represent 4 smooth approaches to replace text with a carriage return in Excel as simply as possible. For further interest, you can comment below.

Related Articles

• How to Replace Text after Specific Character in Excel
• How to Replace Text between Two Characters in Excel

<< Go Back to Excel REPLACE Function | Excel Functions | Learn Excel