Want to change the apartment Number only of same address in a column.

[Leicester City Fox]

Hello There,

Here the situation, I have addresses which many have 50 to 100 apartments in a column.

Instead of me manual having to change the apartment number is there a quicker easier way? See example of results below I want.



Column A



Apartment 1, 10 London Road

Apartment 2, 10 London Road

Apartment 3, 10 London Road

ect

Is there a way of click down so only the apartment No. changes but address stay the same (10 London Road)?



Many Thanks

Leicester Fox

 

[scoudny]

Hi LCF, If you don't mind having helper columns type Apartment 1 in A1 '10 London Road in B1 and =A1&", "&B1 in C1, then drag down.

Might not be the best way but it is quick

 

[Peter_SSs]

You could use a formula like this copied down. Subsequently you could Copy -> PasteSpecial (Values) if you wanted to replace the formulas,

22 06 27.xlsm
	A
1	Apartment 1, 10 London Road
2	Apartment 2, 10 London Road
3	Apartment 3, 10 London Road
4	Apartment 4, 10 London Road
5	Apartment 5, 10 London Road
6	Apartment 6, 10 London Road
7	Apartment 7, 10 London Road
8	Apartment 8, 10 London Road
9	Apartment 9, 10 London Road
10	Apartment 10, 10 London Road
11	Apartment 11, 10 London Road
12	Apartment 12, 10 London Road
Inc Nos

Cell Formulas
Range	Formula
A1:A12	A1	="Apartment "&ROWS(A$1:A1)&", 10 London Road"

 

[Leicester City Fox]

Hi LCF, If you don't mind having helper columns type Apartment 1 in A1 '10 London Road in B1 and =A1&", "&B1 in C1, then drag down.

Might not be the best way but it is quick

Hi Scoudny & Peter SSs

Just a big thank you for both your ideas , I am very grateful as both will work and the easier the better

Many Thanks
The Leicester Fox

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.