Seperate address numbers & street text

[FYRGUY47]

Hello everyone,

Using Excel 2003.

I was just given a large spreadsheet with address' (example: 1234 Milky Way) entered into one cell that I would like to seperate into two cells (1234) & (Milky Way). I need to do this to be able to sort them better.

I attended a advanced Excel class a couple of years ago and remember that there was a function to do this easily. I have lost my notes from that class, and can not find it in my Tips & Tricks, or Formula's books.

Thanks for your help!!

 

[AnotherHiggins]

There are some problems you need to be aware of when attempting this. Like addresses with an apartment or suite number - there will additional elements to deal with.

But for your example,

to get the number:
[tab]=Left(A2, Find("", A2) - 1)

to get the street:
[tab]=Mid(A2, find(" ", A2) + 1, 99)
or
[tab]=Right(A2, Len(A2) - Find(" ", A2))


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[FYRGUY47]

John,

Thanks for your reply... Yes, I have already ran into a couple of small problems. Not only the apartment # issue, but "E" east, & "W" west issues as well. I tried "text to columns" under data and am working my way through that.. I didn't understand the =Mid & =Right part of your formulas. (actually I didn't understand more than that) Do you have any other ideas? I am going to play with your formulas on the sheet and see how the information acts and come up with a plan.. I hope.

Thanks again.
Chris

 

[SkipVought]

I didn't understand the =Mid & =Right part of your formulas. (actually I didn't understand more than that)

That's what the F1 key is for -- Excel HELP.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[FYRGUY47]

Your right... thanks Skip