Hi,
I have a list of addresses that need to be in a different format. The
addresses are currently in this format:
Atria Chandler Villas
101 S. Yucca Street
Chandler, Arizona 85244-8419
(blank cell)
Atria Bell Court Gardens
6653 E. Carondelet Drive
Tucson, Arizona 85710-2155
520.886.3600
(blank cell)
Atria Campana del Rio
1550 East River Road
Tucson, Arizona 85718-5800
520.299.1941

I need them in rows instead of columns, like this:
Atria Chandler Villas 101 S. Yucca Street Chandler, Arizona
85244-8419 Chandler, Arizona 85244-8419

The pattern is as follows for the names:
=B1
=B6
=B11
The names are by 5 but excel AutoFill does not recognize this pattern
to continue by adding 5 to the cell number.

Does anyone know a way to get around this? (fill series doesnt work
with a series of cell names)

Thanks.


--
valoo
------------------------------------------------------------------------
valoo's Profile: http://www.excelforum.com/member.php...o&userid=36892
View this thread: http://www.excelforum.com/showthread...hreadid=566073

start with
C1=B1&"*"&B2
C2=B3&"*"&B4
then
select C1:C5
then select Fill/Copy cross and pull down all the way u got data
then when cells still r selected
Select Copy and then Paste values

now select Data/Text to column
select * as (i dont no the english word for this)
but think u no what i mean





"valoo" skrev:


Hi,
I have a list of addresses that need to be in a different format. The
addresses are currently in this format:
Atria Chandler Villas
101 S. Yucca Street
Chandler, Arizona 85244-8419
(blank cell)
Atria Bell Court Gardens
6653 E. Carondelet Drive
Tucson, Arizona 85710-2155
520.886.3600
(blank cell)
Atria Campana del Rio
1550 East River Road
Tucson, Arizona 85718-5800
520.299.1941

I need them in rows instead of columns, like this:
Atria Chandler Villas 101 S. Yucca Street Chandler, Arizona
85244-8419 Chandler, Arizona 85244-8419

The pattern is as follows for the names:
=B1
=B6
=B11
The names are by 5 but excel AutoFill does not recognize this pattern
to continue by adding 5 to the cell number.

Does anyone know a way to get around this? (fill series doesnt work
with a series of cell names)

Thanks.


--
valoo
------------------------------------------------------------------------
valoo's Profile: http://www.excelforum.com/member.php...o&userid=36892
View this thread: http://www.excelforum.com/showthread...hreadid=566073

Thank you for trying to help but I cannot get your response to work.
It gets confusing when you say FillCross and then copy and paste.
I will keep trying but it seems like you want me to combine the cells
and use the * as a delemiter.


--
valoo
------------------------------------------------------------------------
valoo's Profile: http://www.excelforum.com/member.php...o&userid=36892
View this thread: http://www.excelforum.com/showthread...hreadid=566073

http://pmexcelent.dk/Transpose.xls



"valoo" skrev:


Thank you for trying to help but I cannot get your response to work.
It gets confusing when you say FillCross and then copy and paste.
I will keep trying but it seems like you want me to combine the cells
and use the * as a delemiter.


--
valoo
------------------------------------------------------------------------
valoo's Profile: http://www.excelforum.com/member.php...o&userid=36892
View this thread: http://www.excelforum.com/showthread...hreadid=566073

In cell C1, use:
=OFFSET($B$1,(ROW()-1)*5,0)
Cell D1:
=OFFSET($B$1,(ROW()-1)*5+1,0)
Cell E1:
=OFFSET($B$1,(ROW()-1)*5+2,0)
Cell F1:
=OFFSET($B$1,(ROW()-1)*5+3,0)
Copy the formula down as far as you need to for the 4 columns C to F.

-Simon


"valoo" wrote:


Hi,
I have a list of addresses that need to be in a different format. The
addresses are currently in this format:
Atria Chandler Villas
101 S. Yucca Street
Chandler, Arizona 85244-8419
(blank cell)
Atria Bell Court Gardens
6653 E. Carondelet Drive
Tucson, Arizona 85710-2155
520.886.3600
(blank cell)
Atria Campana del Rio
1550 East River Road
Tucson, Arizona 85718-5800
520.299.1941

I need them in rows instead of columns, like this:
Atria Chandler Villas 101 S. Yucca Street Chandler, Arizona
85244-8419 Chandler, Arizona 85244-8419

The pattern is as follows for the names:
=B1
=B6
=B11
The names are by 5 but excel AutoFill does not recognize this pattern
to continue by adding 5 to the cell number.

Does anyone know a way to get around this? (fill series doesnt work
with a series of cell names)

Thanks.


--
valoo
------------------------------------------------------------------------
valoo's Profile: http://www.excelforum.com/member.php...o&userid=36892
View this thread: http://www.excelforum.com/showthread...hreadid=566073