How to Split the Number From the Street Address in Excel : MS Excel Tips

Subscribe Now:

Watch More:

Splitting the number from the street addresses in Excel is something that you do with a feature called flash fill. Split the number from the street addresses in Excel with help from a Microsoft Certified Applications Specialist in this free video clip.

Expert: Jesica Garrou
Filmmaker: Patrick Russell

Series Description: Microsoft Excel is one of the most powerful spreadsheet and document creation tools in existence. Get tips on Microsoft Excel with help from a Microsoft Certified Applications Specialist in this free video series.

[HardenTheGift]

AWESOME!!! Wow...so much for my formulas education.

[sandyanible]

Jessica, what if the street addresses have a direction? (ex. 101 S MAIN ST)
Using this method with a direction causes the issue in which the direction (by itself) sorts into the same column as those street addresses that have no street direction. So, if I used this method with 101 S MAIN ST, 23 CANARY RD, AND 410 E BROADWAY AVE, my first column would have 101, 23, and 410. But my second column would have S, CANARY, and E. My third column would have MAIN, RD, and BROADWAY. Is there a simple solution for this? I'm wanting to split about 5, 000 lines of addresses.

[penguin]

Your screen capture cuts off where your mouse goe to fill and flash fill! :(

[alrosausa]

Your second method has a flaw. If the Street Name has a compound name with a space in between, the resulting columns will have all kinds of split data, with Street Types mixed in with second or even third Name fields.

[ehenri]

how do you separate just the numbers from the address?

[jessicapinto]

Jes, you're genious!
Hug, other Jess

[OldRunt]

Then I go here. All the way off your screen and hit the mystery button. Now look at that. Just kidding you can't see!

[allothernicksRtaken]

is it just me or video doest show where to click ( FILL)
i hoped this video would solve my problem but it didnt
we have adresses like that: street of somebody 11 or
street 12
so text to columns with space do more harm than good. is there any way to separate letters and numbers like:
|street of somebody| 11|
or even
|street of somebody| 11 | abc | 999|

[phyllissprout]

Some of the commands were not visible on your screen.

[johannespkassing]

This is ridiculous...4 easy examples which are totally the same doesn't explain the different situation which are possible in an address

[TrentGustus]

I came here to find out how to split the number not the rest of the data.. and the only thing you told me here was to buy another version.

[kellyirving]

This great but I only want to split the number in one column and street name in second column like 15/Main St, not 15/ main/street. Is there a way to do that?

[clairtee]

Thanks Jessica! This helped me so much with GIS! *Big Hug*

[sylviabrown]

Thank had a senior moment and could not remember. I always use this step when importing.

[yourkapil]

Hi can you helpe to filer address part like
100-200 south ave to seperate next cloumn would be "to 200"

[richardharmon]

You are Da Bomb!!! Thank you so much :)