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:
Challenge: Now the challenge is to reformat the column C in a way that it may appear as in the below screenshot:
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.
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..
Leave a Reply