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 047 5705 5705 Bassett Trail 033 5710 5710 Bassett Trail 031 5715 5715 Bassett Trail 034 5720 5720 Bassett Trail 030 5725 5725 Bassett Trail 029 5735 5735 Bassett Trail 076 5733 5733 Fall Creek Court 077 5736 5736 Fall Creek Court 075 5743 5743 Fall Creek Court 078 5746 5746 Fall Creek Court 074 5753 5753 Fall Creek Court 079 5756 5756 Fall Creek Court 073 5763 5763 Fall Creek Court 080 5766 5766 Fall Creek Court 072 5773 Fall Creek Court 081 5776 Fall Creek Court 071 5783 Fall Creek Court
7 Replies

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

=MID(C2,1,4)

see that attached file

Hi @Flintstone277

You could also use
=LEFT(C2,4)

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