Combining text from 2 columns and 1000's of cells

[joshny]

So... I am trying to combine text such as the following =(a1&b1&a2&b2&a3&b3&a4&b4....&a5000&b5000) into a single cell.

The problem is Excel will only let me combine 64 cells using this method. I need to combine a few thousand. Almost all of them are blank. There will only be between 1-25 cells in Column A and Column B that have any text. The concatenate method also cannot do this many cells.

I need to figure out how to get them all into one text string in a single cell with no delimeters.

Yes, I know I could create a table and then sort by ascending and then do it manually, but that method won't work for what I am doing. I need to figure out some formula or possibly VBA (though I am not good with Visual Basic) that will allow me to do this without sorting the sheets.

Any ideas. I am using Excel Standard 2013?

Thanks!

[davesexcel]

I think this should do it

Please Login or Register  to view this content.

[joshny]

Thank you very much Dave. That seems to work, although I am having a bit of trouble making it work for other columns now. But I will play with it a bit.