Trying to separate street number and street name into columns

**greenmat** · 07-27-2012, 01:28 PM

Hello,

I'm trying to isolate the street number from the street name. Text to columns splits in incorrect spots, as the data is not in a consistent format, some addresses contain 1/2 or a directional variable, N, S, E, W. Does anyone know how I can do this? Here is a same of the data:

1816 Bellevue Ave
2017 Bellevue Ave
2017-2019 3/4 Bellevue Ave
2711 Bellevue Ave
3000-3004 Bellevue Ave
3001-3007 Bellevue Ave
3408 Bellevue Ave
3517 Bellevue Ave
3601-3603 Bellevue Ave
3617-3619 1/2 Bellevue Ave
203-211 N Benton Way
336 N Benton Way
428 N Benton Way
635-637 N Benton Way
2527-2529 Berkeley Ave
615-619 1/2 N Bonnie Brae St
573 Boylston St

Thanks for the help.

**BenMiller** · 07-27-2012, 01:38 PM

So the 1/2 or 3/4 would be part of the number, not the street, right?

If yes, try this formula:

=CHOOSE(ISNUMBER(FIND("/",A1))+1,A1,REPLACE(A1,FIND("/",A1)-2,1,"&"))

Then copy/paste values, text to columns delimited on a space, then find/replace & with a space

Cool?

**day92** · 07-27-2012, 01:46 PM

This formula will extract the numbers only.

=LOOKUP(99^99,--("0"&MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW($1:$10000))))

**greenmat** · 07-27-2012, 02:02 PM

Thanks Ben and Day92,

I was successful with Ben's suggestion.

Now, does anyone know how to to a text to columns for only the first space? This way the street number is in one column and the remainder is in another, for example, N Main Street would result in one column, not three columns.

**BenMiller** · 07-27-2012, 02:08 PM

You can always use =SUBSTITUTE(A1," ","%",1) and copy/paste values then do text to columns on the %

**greenmat** · 07-27-2012, 02:18 PM

Great! It worked. Thanks for the help!

**BenMiller** · 07-27-2012, 02:42 PM

No problem

Glad I could help ... Appreciate the feedback!

**Cutter** · 07-27-2012, 04:24 PM

@ greenmat

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.