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.

Download Practice Workbook

4 Smooth Approaches to Replace Text with Carriage Return in Excel

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: Excel VBA to Find and Replace Text in a Column (2 Examples)

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 Insert Carriage Return in Excel Cell (3 Simple Ways)

Similar Readings

• Excel VBA: Open Word Document and Replace Text (6 Examples)
• [Fixed!] Carriage Return Not Working in Excel (2 Solutions)
• Excel VBA: How to Find and Replace Text in Word Document
• How to Remove Carriage Returns in Excel: 3 Easy Ways
• Replace Text after Specific Character in Excel (3 Methods)

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: How to Replace Text in Excel Formula (7 Easy Ways)

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.

Read More: Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)

Practice Section

For more expertise, you can practice here.

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

• Excel VBA to Replace Blank Cells with Text (3 Examples)
• How to Replace Carriage Return with Comma in Excel (3 Ways)
• Replace Text with Blank Cell in Excel (5 Simple Methods)
• Find Carriage Return in Excel (2 Easy Methods)
• Excel VBA: How to Replace Text in String (5 Effective Ways)
• Remove Carriage Return in Excel with Text to Columns
• How to Replace Text in Selected Cells in Excel (4 Simple Methods)