an address in a row in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 6352 mailing addresses in a column, I would like to organize under
Company, Adresse 1 Addresses 2, city, state zip phone etc in a row. is there
a formular I can use in excel to do this
 
This might work for you ..

Let's assume the sample data below
is in Sheet1, in col A, A1 down

The data is assumed to be in groups of 7 lines each
with 1 blank row in-between each group, viz.:

Company1
Address1
Address2
City1
State1
Zip1
Phone1

Company2
Address1
Address2
City2
State2
Zip2
Phone2
etc

In Sheet2
-------------
Let's leave row1 for col headers (labels)

Put in A2:

=OFFSET(Sheet1!$A$1,ROW(A1)*8-8+COLUMN(A1)-1,)

Copy A2 across to G2 (i.e. across a total of 8 columns),
then copy down until zeros appear
signalling exhaustion of data

(since it's stated that you have 6352 groups of data,
just copy down to row6354 thereabouts <g>)

The above will extract the data from col A in Sheet1
and re-lay it nicely in a column-wise manner in Sheet2
viz. :

Company1 Address1 Address2 City1 State1 Zip1 Phone1
Company2 Address1 Address2 City2 State2 Zip2 Phone2
etc
 
I want to put addresses like eg:
Austin Tec Credit Union
114 East Huntland Dr.
Austin, TX 78752

under the column headings
Company, Adresse 1, Addresses 2, city, state
zip phone
Austinn Tec 114 East Hutland Austin TX
78752

at the present I have them all in a block in one coulmn.
 
Think it's tough to get it exactly, but here's one attempt
(Try on a *spare copy* of your workbook)
which may bring you a little closer to your stated objective ..

Assume the sample data below
is in Sheet1, in col A, A1 down

The data is assumed to be in groups of 3 lines each
with 1 blank row in-between each group, viz.:

Austin Tec Credit Union
114 East Huntland Dr.
Austin, TX 78752

Maryland Credit Union
190 West Huntland Dr.
Austin, TX 78752

etc

In Sheet2
-------------
Leave row1 for col headers (labels)

Put in A2:
=OFFSET(Sheet1!$A$1,ROW(A1)*4-4+COLUMN(A1)-1,)

Copy across to C2

A2:C2 will re-lay the data
from Sheet1's A1:A3 (the 1st group of data)
into 3 columns: Company, Address & City-State-Zip

Put in D2: =SUBSTITUTE(TRIM(C2),",","")

D2 will remove the comma in col C which
appears after "City", and is to prepare the ground
to split col D's City-State-Zip via Data > Text to Columns
(steps given later below)

Select A2:D2 and copy down until zeros appear
signalling exhaustion of data

(As it's stated earlier that you have 6352 groups
of data, just copy down to row6354 thereabouts ..)

Now to "kill" the formulas in cols A to D
-------------------------------------------------
Press CTRL+A
(this selects the entire sheet)
Right click > Copy
Right-click > Paste special > Values > OK

Splitting col D (City-State-Zip)
------------------------------------
Select the data range in col D
(e.g. select D2:D6354)

Click Data > Text to columns
("Fixed width" will be selected)

Click Finish

This will split the data in col D (city, state, zip)
into 3 separate cols

Now just delete col C, and the results will appear
in cols A to E: Company, Address, City, State, Zip
(Finish up by putting the above labels into A1:E1)

[ I hope my understanding of the
US' "City-State-Zip" is correct <g> ]
 
Back
Top