How to delete blank cells from a table?

[bk307]

Hello, I'm trying to find a way to delete all blank cells from a table in excel. Each cell is either blank or contains a 3 letter code (example below).

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="class: grid, width: 0"]
<tbody>[TR]
[TD]aaa[/TD]
[TD]ada[/TD]
[TD]bdd[/TD]
[TD]bdg[/TD]
[TD]ger[/TD]
[TD]3r2[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]bdd[/TD]
[TD][/TD]
[TD]ger[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bbb[/TD]
[TD][/TD]
[TD]bdd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD][/TD]
[TD][/TD]
[TD]rw3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aca[/TD]
[TD]drg[/TD]
[TD][/TD]
[TD][/TD]
[TD]geg[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like to remove all of the blank cells to end up with the following:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="class: grid, width: 0"]
<tbody>[TR]
[TD]aaa[/TD]
[TD]ada[/TD]
[TD]bdd[/TD]
[TD]bdg[/TD]
[TD]ger[/TD]
[TD]3r2[/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]bdd[/TD]
[TD]ger[/TD]
[TD]ger[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]bbb[/TD]
[TD]bdd[/TD]
[TD]bdd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aaa[/TD]
[TD]rw3[/TD]
[TD]rw3[/TD]
[TD]rw3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]aca[/TD]
[TD]drg[/TD]
[TD]geg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

My data set has around 90,000 rows and each row contains between 5 to 25 codes, with blank cells intermixed. I would also like to get all of the distinct codes in this data set.

Anyone have any insights?

Thanks

 

[JefferyLi]

How many columns you have?

if not too many, you can do this. (For example you have 5 columns, column A - E)
1. On the first right blank column. Enter this formula to combine them: =A1&B1&C1&D1&E1. Drag it down.
2. Copy and paste as value. Remove formula.
3. Use text to column, fixed width. Separate them by 3 letters each.