text to table?

[gerry.lisa]

XL 2003

I have about 150 groups that I would like to put into rows in Excel...
1 row/group.

They are like this:

WOODSTOCK & DISTRICT BIG BROTHERS ASSOC. INC.
378 Hunter St
Woodstock, ON N4S 4G2
(519) 539-7469
(e-mail address removed)
www.bigbrotherswoodstock.ca

BIG BROTHERS BIG SISTERS OF YORK
PO Box 126, 145 Main St. S
Newmarket, ON L3Y 3Y9
(905) 895-0289
(e-mail address removed)
www.bbbsy.ca

There are tabs at the end of each and every line.
(or if I paste the data unformatted, there are returns at the end of
each and every line.)

I would like 1 row and 6 columns per group. I know I can copy and
paste special transpose each group but I have lots of them!
Is there an easier way to do this?

....Lisa

 

[Toppers]

If there are always 6 lines and a blank line between each group, then try:

With data in column A (A1):

in B1:


=INDIRECT("A"&(INT(ROW()-1)*7+COLUMN()-1))

Copy across required columns and down until blank entries reached.

 

[Rick Rothstein \(MVP - VB\)]

XL 2003

I have about 150 groups that I would like to put into rows in Excel...
1 row/group.

They are like this:

WOODSTOCK & DISTRICT BIG BROTHERS ASSOC. INC.
378 Hunter St
Woodstock, ON N4S 4G2
(519) 539-7469
(e-mail address removed)
www.bigbrotherswoodstock.ca

BIG BROTHERS BIG SISTERS OF YORK
PO Box 126, 145 Main St. S
Newmarket, ON L3Y 3Y9
(905) 895-0289
(e-mail address removed)
www.bbbsy.ca

There are tabs at the end of each and every line.
(or if I paste the data unformatted, there are returns at the end of
each and every line.)

I would like 1 row and 6 columns per group. I know I can copy and
paste special transpose each group but I have lots of them!
Is there an easier way to do this?

See if this macro does what you want (shown here being executed in response
to a CommandButton click)...

Private Sub CommandButton1_Click()
Dim X As Long
Dim R As Range
Dim Txt As String
Dim Lines() As String
On Error GoTo Done
For Each R In Range("A1:A200")
Txt = R.Value
If InStr(Txt, Chr$(9)) Then
Txt = Replace(Txt, Chr$(9), vbLf)
ElseIf InStr(Txt, vbCrLf) Then
Txt = Replace(Txt, vbCrLf, vbLf)
End If
Lines = Split(Txt, vbLf)
For X = 0 To 5
Cells(R.Row, R.Column + X).Value = Lines(X)
Next
Next
Done:
End Sub

I assumed your data is in column A starting at row 1 (although you said you
had 150 groups, I allowed for up to 200, but you can change that if you
want). If your data is in a different column, or starts at a different row,
then make the necessary change in the Range object reference in the For Each
statement.

Rick

 

[Gord Dibben]

Gerry

Easiest and quickest is with a macro but note the formulas methos posted at
bottom.

Sub ColtoRows()
Dim Rng As Range
Dim I As Long
Dim j As Long
Dim nocols As Long
Set Rng = Cells(Rows.Count, 1).End(xlUp)
j = 1
On Error Resume Next
nocols = InputBox("Enter Number of Columns Desired")

For I = 1 To Rng.Row Step nocols
Cells(j, "A").Resize(1, nocols).Value = _
Application.Transpose(Cells(I, "A").Resize(nocols, 1))
j = j + 1
Next
Range(Cells(j, "A"), Cells(Rng.Row, "A")).ClearContents

End Sub

You do not really need a macro to accomplish your task BTW.

In B1 enter this formula................

=INDEX($A:$A,(ROWS($1:1)-1)*6+COLUMNS($A:B)-1)

Copy across to Column E then select B1:G1 and copy down untill you get zeros.


Gord Dibben MS Excel MVP