Find and Replace carriage return in MS Excel

Scenario: Here is a scenario that I have come across recently in Excel. I have the data in column that is split across multiple lines in each cell, more technically speaking, separated by carriage returns. i.e., the text in the cell is separated by pressing ALT + ENTER. see the below screenshot:

Excel_Find&Replace_CarriageReturn

Challenge: Now the challenge is to reformat the column C in a way that it may appear as in the below screenshot:

Excel_Find&Replace_CarriageReturn3

Solution: We will have to find and replace the carriage return with a comma followed by a space (i.e., “, “). Invoke the Find and Replace dialogue hitting Ctrl + H on the keyboard. Now place the cursor in the Find What text box and enter the value by hitting Ctrl + J. When you do this, the value in Find What text box appears like a very small blinking dot. Now enter a comma followed by a space in the Replace With text box. Then hit on Replace All button. Excel now pops up a message indicating the number of places the replace occurred, as in the below screenshot.

Excel_Find&Replace_CarriageReturn2

Now expand the width of column C and contract the height of all rows, so that table appears as desired.

Remember that Ctrl + J is the hotkey combination for Carriage Return.

Hope you enjoyed reading this post. Want to master Excel with more such techniques..? Follow my blog by entering your email and then hitting on FOLLOW button at the top right.

Do leave your comments or questions, if any..

About these ads

Tagged: , , , , , , , , , , ,

Your valuable comments are highly honoured..!!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: