1 Column to multiple rows macro

[MrFundraising]

I'm looking for a macro to take 1 column of data and create multiple rows. Right now my data looks like:

Name
Company
Address
City State Zip
Phone
Fax
Email

Name
Company
Address
City State Zip
Phone
Fax
Email

I need each line to be in its own column so that I can import the data into another program.

They are all separated by a space and the names are always first and in bold. However, the number of lines of info isn't always the same. Sometimes there is no phone or fax number.

I am using Office 2010.

Any help would be much appreciated. Thank you!

 

[VoG]

Try this with a copy of your sheet

Sub Addrs()
Dim Area As Range
For Each Area In Columns("A").SpecialCells(xlCellTypeConstants).Areas
    Area(1).Offset(, 1).Resize(, Area.Rows.Count).Value = Application.Transpose(Area)
Next Area
Columns("A").Delete
Columns("B").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

 

[MrFundraising]

That worked perfectly! Thank you so much!!!