Splitting Data in Cell

I have a database that lists addresses in the following format:
111 Maple Dr.

I want to split the street number from the Street name so that I can
sort on the street name only. Is this possible?

helper column:
=MID(A1,FIND(" ",A1)+1,255)
fill down
sort on this column
Bob Umlas
Excel MVP

wrote in message
...
I have a database that lists addresses in the following format:
111 Maple Dr.

I want to split the street number from the Street name so that I can
sort on the street name only. Is this possible?

Maybe this:

A1 = 111 Maple Dr

B1 formula:

=MID(A1,FIND(" ",A1)+1,100)

Then sort on column B.

--
Biff
Microsoft Excel MVP


wrote in message
...
I have a database that lists addresses in the following format:
111 Maple Dr.

I want to split the street number from the Street name so that I can
sort on the street name only. Is this possible?

Thanks Bob, That did the trick. Just for my enlightenment, can you
explain why it [=MID(A1,FIND(" ",A1)+1,255) ] is writtrn in that
format and how it works?

On Tue, 16 Jun 2009 14:17:26 -0400, "Bob Umlas"
wrote:

helper column:
=MID(A1,FIND(" ",A1)+1,255)
fill down
sort on this column
Bob Umlas
Excel MVP

wrote in message
.. .
I have a database that lists addresses in the following format:
111 Maple Dr.

I want to split the street number from the Street name so that I can
sort on the street name only. Is this possible?

The MID function returns substrings located in larger strings of text. The
first argument is the string of text you want to pull the substring out of;
the second argument is the position within the string of text you want to
start at when pulling out the substring and the third argument is the number
of characters making up your substring. The second argument in Bob's formula
uses the FIND function to locate the first blank space which is located
between your number and the street name (FIND returns the position number of
the character(s) you are trying to find)... the 1 is added to it because you
don't want to start pulling your substring out at the space, you want to do
that from the character after the space. The 255 that Bob used as his third
argument is just a number that is assumed to be bigger than the total number
of characters that could possibly make up a street name (that way, you get
all the text after the first space).

--
Rick (MVP - Excel)


wrote in message
...
Thanks Bob, That did the trick. Just for my enlightenment, can you
explain why it [=MID(A1,FIND(" ",A1)+1,255) ] is writtrn in that
format and how it works?

On Tue, 16 Jun 2009 14:17:26 -0400, "Bob Umlas"
wrote:

helper column:
=MID(A1,FIND(" ",A1)+1,255)
fill down
sort on this column
Bob Umlas
Excel MVP

wrote in message
. ..
I have a database that lists addresses in the following format:
111 Maple Dr.

I want to split the street number from the Street name so that I can
sort on the street name only. Is this possible?

Thanks Rick,

The explanation will help me immensely in the future.

On Tue, 16 Jun 2009 16:23:35 -0400, "Rick Rothstein"
wrote:

The MID function returns substrings located in larger strings of text. The
first argument is the string of text you want to pull the substring out of;
the second argument is the position within the string of text you want to
start at when pulling out the substring and the third argument is the number
of characters making up your substring. The second argument in Bob's formula
uses the FIND function to locate the first blank space which is located
between your number and the street name (FIND returns the position number of
the character(s) you are trying to find)... the 1 is added to it because you
don't want to start pulling your substring out at the space, you want to do
that from the character after the space. The 255 that Bob used as his third
argument is just a number that is assumed to be bigger than the total number
of characters that could possibly make up a street name (that way, you get
all the text after the first space).