Copy to next cell until CAPITAL words

[djfreex]

COMPANY A
555 FAKE ST
555-555-5555
NEW YORK, NY 5555
Phone: (222) 222-2222
Fax: (222) 222-2222
COMPANY B
555 FAKE ST
555-555-5555
NEW YORK, NY 5555
Phone: (222) 222-2222
Fax: (222) 222-2222

I have data set of addresses like above in a spreadsheet that I want to convert to a address book like format - name,address, phone etc in different columns. Addresses are not always 6 lines - might be 4 or 5.

Whats the most efficient way to get this accomplished? Thanks!

 

[GTO]

You need something to identify the start or end of a record (preferably both). Does the last line of ea record ALWAYS start w/Fax: ?

 

[djfreex]

Start is always CAPITAL letters and end line is not always fax - some addresses dont have fax, but most do. Thanks.

 

[djfreex]

Start is always CAPITAL letters and end line is not always fax - some addresses dont have fax, but most do. Thanks.

Not a big deal to have fax.we can assume it always ends with Phone.

 

[rassten]

Maybe something like this,
start with, copy the data to cell A1 in a blank sheet, make sure there is no blank rows. Then run this macro

Sub b_test()

fr1 = Cells(Rows.Count, 1).End(xlUp).Row
Range("a" & fr1 + 1) = "STOP"

y = 1
x = 1

For i = 2 To fr1 + 1

    If UCase(Range("A" & i)) = Range("A" & i) Then
    Range(Range("A" & y), Range("A" & i - 1)).Copy
    Range("B" & x).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
        y = i
        x = x + 1
     End If
Next

Range("a" & fr1 + 1).ClearContents
End Sub