Text to Columns using VBA

 

Past Tip of the Day

 

Travis asks "I am trying to format some text into separate columns. The text is in one
column as follows:


Column 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


Column A                       Column B          Column 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-v 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 <= lastrowRange(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

By Juan Pablo Gonzalez on 10-Feb-2002
MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.