Excel spreadsheet creation

Occasional Contributor

I have a spreadsheet that I am working with that has a lot of information already entered.  I want to creat a new column that has the "house Number" only in this new column, and it is adjacent to the old column that has the full address in it including the house number.   HOW DO I enter the first few cells in the top of the new column that duplicates the old column but ONLY has the house number and not the rest of the address information?    I used to be able to do this in Excel by typing the house number in the first few cells and the Automatic Cell completion capabilities would allow me to highlight those cells and drag down while the Excel engine would copy just the house number from the old column  and paste it in the new column.  Here is a snapshot of what I am trying to do:

Lot Number Address1
04757055705 Bassett Trail
03357105710 Bassett Trail
03157155715 Bassett Trail
03457205720 Bassett Trail
03057255725 Bassett Trail
02957355735 Bassett Trail
07657335733 Fall Creek Court
07757365736 Fall Creek Court
07557435743 Fall Creek Court
07857465746 Fall Creek Court
07457535753 Fall Creek Court
07957565756 Fall Creek Court
07357635763 Fall Creek Court
08057665766 Fall Creek Court
072 5773 Fall Creek Court
081 5776 Fall Creek Court
071 5783 Fall Creek Court
7 Replies

Hi @Flintstone277 

I notice that the house number is only the first 4 digits, if that apply to all, you can use this formula

C2= Address 



see that attached file

Hi @Flintstone277

You could also use


In general AutoFill works if empty column to the left and fill to the right.


Sergei, That is What I was doing. I had the Address column in column E and I inserted a new column in D and then I began to fill in the HOUSE NUMBER in D1, D2, D3 and so on. Then after a few were filled in, I HIGHLIGHTED D1-D6 and tried to pull the bottom right corner of the highlighted area down so it would auto-fill, but it does NOT recognize that the data I want is from the adjacent cells E1-E6. It thinks I am filling in some mathematic formula and does NOT pick up the House numbers from the adjacent cells. I USED TO DO THIS with no problem in past efforts, but it seems Excel has changed the way auto-fill works and it will not look for the correlation to the adjacent cells and see that pattern. HOW CAN I DO THIS NOW????
SERGEI, OH! Now I see, I'm on the wrong side! Thanks!! It didn't used to be that way, but now that I know, I can use it. And thanks to all the other suggestions! I'll try them too!
Jihad, I LIKE that formula! It works! Thank you!!!
NOBINKJ, THANK YOU!!! That works too!! Thanks!!!!!!!!!!