How unorganized data could drive you crazy!

This is about how to clean up data for a simple VLOOKUP task.  To be more specific, how to separate data delimited by line break in a cell, as you see in the following screen shot.

Excel Tips - Separate data by line break in cell 1

The one who input record like this should have plenty of time; or expect you to have nothing to do; or simply because s/he just learned how to input line break in a cell… I hope you will never encounter a file like this.  Nevertheless just in case you do, you know a way to clean it up. 🙂

Text to Columns comes to rescue.  How?

First let’s transpose the data; Copy and Paste (Transpose)

Excel Tips - Separate data by line break in cell 2

Once you have your data transposed, select the two cells you need to separate the data, with the help of Text to Columns.  (Note: make sure you have enough space to the right for the text to expand!)

Go to Data Tab –> Text to Columns –> Delimited –> NextExcel Tips - Separate data by line break in cell 3

Here comes the tricky part:

Check “Others:” and then click into the text box next to it –> Press Ctrl+J  (Observe the changes in the Data Preview)

Excel Tips - Separate data by line break in cell 4

Now it’s ready to FINISH.

The result should look like the screen shot below.  Please double-click inbetween the row labels to autofit the row height.

Excel Tips - Separate data by line break in cell 5

Although the data is now ready for HLOOKUP, let’s transpose it back to have the look and feel of the original data.

Excel Tips - Separate data by line break in cell 6

Not too difficult, right?!

Don’t know VLOOKUP?  No worry, I have many posts of VLOOKUP for your reference.

Please DO NOT input various data in a single cell by inserting line break just because you know how to input line break.

If you want to remove line break quickly, please read this.


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.