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


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.

How to Replace Text with Carriage Return in Excel

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.

How to Replace Text with Carriage Return in Excel

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.

How to Replace Text with Carriage Return in Excel

  • 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.

How to Replace Text with Carriage Return in Excel

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


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.

How to Replace Text with Carriage Return in Excel

  • Input the following formula:
=SUBSTITUTE(C5,"Manchester United",CHAR(10))

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.

How to Replace Text with Carriage Return in Excel

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

How to Replace Text with Carriage Return in Excel

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.

How to Replace Text with Carriage Return in Excel

  • 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

How to Replace Text with Carriage Return in Excel

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.

How to Replace Text with Carriage Return in Excel

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

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo