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.

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)

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 –> Next

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)

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.

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

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.