# Extracting Address Unit Number and Combine it in another cell

#### extremedownline

##### New Member
Hi,

I having the following cells in my excel sheet:

 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:

 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!!!

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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

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

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

Replies
12
Views
387
Replies
11
Views
526
Replies
0
Views
162

1,196,497
Messages
6,015,553
Members
441,900
Latest member
Inaschemitex2023

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back