Extracting Address Unit Number and Combine it in another cell

[extremedownline]

Hi,

I having the following cells in my excel sheet:

Address1 Address2

1 COLEMAN STREET #02-03A	THE ADELPHI
15 BEACH ROAD, #03-051	BEACH CENTRE, SINGAPORE
5001 BEACH RD #09-02	GOLDEN MILE COMPLEX
5001 BEACH ROAD #02-342	GOLDEN MILE COMPLEX
6001 BEACH ROAD #16-56	GOLDEN MILE TOWER

<tbody>
</tbody>

I would like to extract all unit number from Address 1 eg. #02-03A to Address 2 . End product should look like this:

Address1 Address2

1 COLEMAN STREET	#02-03A THE ADELPHI
15 BEACH ROAD,	#03-051 BEACH CENTRE, SINGAPORE
5001 BEACH RD	#09-02 GOLDEN MILE COMPLEX
5001 BEACH ROAD	#02-342 GOLDEN MILE COMPLEX
6001 BEACH ROAD	#16-56 GOLDEN MILE TOWER

<tbody>
</tbody>

Appreciate someone could help me out... Thanks!!!

 

[L. Howard]

One way.

Make sure you have a blank column to the right of address 1 list.
Select the address 1 column > Data > Text to columns > Delimited > other > # > Finish

Use a formula like = D1&" "&E1 to combine the new column with the address 2 list and pull down.
Select the combined column > Copy > Paste Special > Values to get rid of the formulas.

Move the new list to where ever you want on the sheet.

Regards,
Howard

 

[Weazel]

if you add a column you can try ....

=MID(A2,FIND("#",A2),LEN(A2)-FIND(" ",A2))&" "&B2

A2 being the first address and B2 being the second address in C2 paste the formula and drag it down

 

[extremedownline]

if you add a column you can try ....

=MID(A2,FIND("#",A2),LEN(A2)-FIND(" ",A2))&" "&B2

A2 being the first address and B2 being the second address in C2 paste the formula and drag it down

What if the address1 unit number is not always in order. Eg:
#02-03A 1 COLEMAN STREET
#03-051 15 BEACH ROAD,

Thanks for your reply.