Text to Columns Using VBA
February 10, 2002 - by Juan Pablo Gonzalez
I am trying to format some text into separate columns. The text is in one column as follows:
A Company Name A PO BOX 1 NEWARK NJ 07101 Company Name B PO BOX 2 1400 SANS SOUCI PARKWAY WILKES-BARRE PA 18703 Company Name C PO BOX 3 CHICAGO IL 60694 Company Name D PO BOX 4 CLEARFIELD UT 84015 Company Name E PO BOX 5 OGDEN UT 84401 Company Name F PO BOX 6 BALTIMORE MD 21264-4351
When I formatted the test to excel I made this column as illustrated above. I would like to know of a formula to which I can make the data appear on one row rather than in multiple rows for on company the formula should result if giving me that following in one row but in different columns:
All on the same ROW
A B C Company Name A PO BOX 1 NEWARK NJ 07101
I have use vlookups and hlookups and pivot tables but I can’t figure out how to get the date if a row-by-row way.
I have been able to get company names to appear row-by-row in one column by sorting and I figured that I could use a combination of a vlookup with a hlookup to give me the results I was looking for but I do not know the proper formula to nest in the ether lookup. I’ve also tested some DSUM function but none are working for me? So please let me know what or how to fix my problem.
I think this can be easily achivied using a macro. Try this one.
Sub CopyAcross() Dim i As Long Dim NRow As Long Dim LastRow As Long NRow = 2 'First Row TO COPY, change this if you want i = 2 'First Row where the data appears, I assumed it started in A2 LastRow = Range("A65536").End(xlUp).Row While i <= LastRow Range(Cells(i, 1), Cells(i, 1).End(xlDown)).Copy Cells(NRow, 2).PasteSpecial Transpose:=True 'In here, I'm copying to Column 2, Next availabe Row. You can change the 2 if you want. NRow = NRow + 1 i = Cells(i, 1).End(xlDown).End(xlDown).Row Wend End Sub