Text to Columns Using VBA

February 10, 2002 - by Juan Pablo Gonzalez

Travis asks:

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