trying to sort/farmat scanned info

[eternallygrateful]

I saved a scan of a label sheet as an excel file. Each piece of info is being
allocated its own cell. Right now it is in the format:
<name1><name2><name3>
<street1><street2><street3>
<state+1><state+2><state3+>
<name4><name5><name6>
<street4><street5><street6>
<state+4><state+5><state+6>
<name7><name8><name9>
<street7><street8><street9>
<state+7><state+8><state+9>
..
..
..
....and continues in this manner for a few hundred pages.
I want to be able to manipulate the info so it's in the format:
<name1><street address1><city,state,zip1>
<name2><street address2><city,state,zip2>
<name3><street address3><city,state,zip3>
..
..
..
....how can i do this? I am a relative excel noobie and don't even know where
to start. The fact that the info isnt a simple text or ascii file with
delimiters doesnt help the matter. Any and all help is appreciated

 

[Peo Sjoblom]

Try something like

=OFFSET($A$1,COLUMN(A:A)-1,ROW(1:1)-1)

copy across 4 columns then copy down as long as needed,
once you start getting numbers you are done,
replace $A$1 with the left uppermost data cell

I used 4 columns although the first part of you exampole don't have zip
codes but your second part do have them, if the zips are together with the
states only copy 3 columns accross


Regards,

Peo Sjoblom

 

[eternallygrateful]

i highlighted the four columns and down to the last row (36th row) of my test
spreadsheet and then copied the formula you provided. Cell A1 which
originally contained a bunch of numbers (i.e. <info+1>) changed to 0 and the
rest of cells remained unchanged. I must be doing something wrong. Thnx in
advance for your help.