I am trying to clean up an address table in excel. Here is example of
the row I am working with:

Northgate I Apt 342
Northgate I Apt 242
Apt 242 Northgate I
Apt 43 Northgate I

I want to take each address and create two new columns, one for
"Northgate I" and one for Apt###, with whatever apartment # is in the
cell. One of the problems is that the cells are not uniformly name/
apart#, but rather, in all sorts of combinations. There are never more
than 3 numbers after "Apt", and the numbers always come after "Apt"

Any help or advice would be great. I am also looking to learn some
VBA. Any websites or books you recommend for learning VBA for Excel?

Thanks,
Michael

On 17 Mar 2007 19:21:06 -0700, wrote:

I am trying to clean up an address table in excel. Here is example of
the row I am working with:

Northgate I Apt 342
Northgate I Apt 242
Apt 242 Northgate I
Apt 43 Northgate I

I want to take each address and create two new columns, one for
"Northgate I" and one for Apt###, with whatever apartment # is in the
cell. One of the problems is that the cells are not uniformly name/
apart#, but rather, in all sorts of combinations. There are never more
than 3 numbers after "Apt", and the numbers always come after "Apt"

Any help or advice would be great. I am also looking to learn some
VBA. Any websites or books you recommend for learning VBA for Excel?

Thanks,
Michael

Here's one way:

Download and install LOngre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then, with data in A1, use these formulas:

Apt number
B1: =REGEX.MID(A1,"Apt\s*\d+")

Rest of string
C1: =TRIM(SUBSTITUTE(A1,B1,""))
--ron

Try this using the built-in functions:

For the apt number:

A2 = Northgate I Apt 342

Formula entered in B2:

="Apt
"&LOOKUP(10^10,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:255"))))

For the name:

Formula entered in C2:

=TRIM(SUBSTITUTE(A2,B2,""))

Book list:

http://contextures.com/xlbooks.html

Biff

wrote in message
ps.com...
I am trying to clean up an address table in excel. Here is example of
the row I am working with:

Northgate I Apt 342
Northgate I Apt 242
Apt 242 Northgate I
Apt 43 Northgate I

I want to take each address and create two new columns, one for
"Northgate I" and one for Apt###, with whatever apartment # is in the
cell. One of the problems is that the cells are not uniformly name/
apart#, but rather, in all sorts of combinations. There are never more
than 3 numbers after "Apt", and the numbers always come after "Apt"

Any help or advice would be great. I am also looking to learn some
VBA. Any websites or books you recommend for learning VBA for Excel?

Thanks,
Michael

Thanks for your help! Works well.

Ron Rosenfeld wrote:
On 17 Mar 2007 19:21:06 -0700, wrote:

I am trying to clean up an address table in excel. Here is example of
the row I am working with:

Northgate I Apt 342
Northgate I Apt 242
Apt 242 Northgate I
Apt 43 Northgate I

I want to take each address and create two new columns, one for
"Northgate I" and one for Apt###, with whatever apartment # is in the
cell. One of the problems is that the cells are not uniformly name/
apart#, but rather, in all sorts of combinations. There are never more
than 3 numbers after "Apt", and the numbers always come after "Apt"

Any help or advice would be great. I am also looking to learn some
VBA. Any websites or books you recommend for learning VBA for Excel?

Thanks,
Michael

Here's one way:

Download and install LOngre's free morefunc.xll add-in from
http://xcell05.free.fr/

Then, with data in A1, use these formulas:

Apt number
B1: =REGEX.MID(A1,"Apt\s*\d+")

Rest of string
C1: =TRIM(SUBSTITUTE(A1,B1,""))
--ron

Thanks for your help! The first formula worked great. Tbe second
formula is simply returning whatever was in the first cell. I think
that is because the first formula returns "Apt###" and not "Apt ###"
which is what is A2.

On Mar 17, 11:17 pm, "T. Valko" wrote:
Try this using the built-in functions:

For the apt number:

A2 = Northgate I Apt 342

Formula entered in B2:

="Apt
"&LOOKUP(10^10,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:255"))))

For the name:

Formula entered in C2:

=TRIM(SUBSTITUTE(A2,B2,""))

Book list:

http://contextures.com/xlbooks.html

Biff

wrote in message

ps.com...

I am trying to clean up an address table in excel. Here is example of
the row I am working with:

Northgate I Apt 342
Northgate I Apt 242
Apt 242 Northgate I
Apt 43 Northgate I

I want to take each address and create two new columns, one for
"Northgate I" and one for Apt###, with whatever apartment # is in the
cell. One of the problems is that the cells are not uniformly name/
apart#, but rather, in all sorts of combinations. There are never more
than 3 numbers after "Apt", and the numbers always come after "Apt"

Any help or advice would be great. I am also looking to learn some
VBA. Any websites or books you recommend for learning VBA for Excel?

Thanks,
Michael

On 19 Mar 2007 09:16:52 -0700, "
wrote:

Thanks for your help! Works well.


You're welcome. Thanks for the feedback.
--ron

That's because the first formula got messed up due to line wrap. There is a
space after "Apt":

="Apt
"&LOOKUP(10^10.............

Should be:

="Apt "&LOOKUP(10^10............

Biff

wrote in message
ups.com...
Thanks for your help! The first formula worked great. Tbe second
formula is simply returning whatever was in the first cell. I think
that is because the first formula returns "Apt###" and not "Apt ###"
which is what is A2.

On Mar 17, 11:17 pm, "T. Valko" wrote:
Try this using the built-in functions:

For the apt number:

A2 = Northgate I Apt 342

Formula entered in B2:

="Apt
"&LOOKUP(10^10,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:255"))))

For the name:

Formula entered in C2:

=TRIM(SUBSTITUTE(A2,B2,""))

Book list:

http://contextures.com/xlbooks.html

Biff

wrote in message

ps.com...

I am trying to clean up an address table in excel. Here is example of
the row I am working with:

Northgate I Apt 342
Northgate I Apt 242
Apt 242 Northgate I
Apt 43 Northgate I

I want to take each address and create two new columns, one for
"Northgate I" and one for Apt###, with whatever apartment # is in the
cell. One of the problems is that the cells are not uniformly name/
apart#, but rather, in all sorts of combinations. There are never more
than 3 numbers after "Apt", and the numbers always come after "Apt"

Any help or advice would be great. I am also looking to learn some
VBA. Any websites or books you recommend for learning VBA for Excel?

Thanks,
Michael

Or, if you don't want "Apt ###" and just want the number by itself:

=LOOKUP(10^10,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:255"))))

Biff

"T. Valko" wrote in message
...
That's because the first formula got messed up due to line wrap. There is
a space after "Apt":

="Apt
"&LOOKUP(10^10.............

Should be:

="Apt "&LOOKUP(10^10............

Biff

wrote in message
ups.com...
Thanks for your help! The first formula worked great. Tbe second
formula is simply returning whatever was in the first cell. I think
that is because the first formula returns "Apt###" and not "Apt ###"
which is what is A2.

On Mar 17, 11:17 pm, "T. Valko" wrote:
Try this using the built-in functions:

For the apt number:

A2 = Northgate I Apt 342

Formula entered in B2:

="Apt
"&LOOKUP(10^10,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"01234567 89")),ROW(INDIRECT("1:255"))))

For the name:

Formula entered in C2:

=TRIM(SUBSTITUTE(A2,B2,""))

Book list:

http://contextures.com/xlbooks.html

Biff

wrote in message

ps.com...

I am trying to clean up an address table in excel. Here is example of
the row I am working with:

Northgate I Apt 342
Northgate I Apt 242
Apt 242 Northgate I
Apt 43 Northgate I

I want to take each address and create two new columns, one for
"Northgate I" and one for Apt###, with whatever apartment # is in the
cell. One of the problems is that the cells are not uniformly name/
apart#, but rather, in all sorts of combinations. There are never more
than 3 numbers after "Apt", and the numbers always come after "Apt"

Any help or advice would be great. I am also looking to learn some
VBA. Any websites or books you recommend for learning VBA for Excel?

Thanks,
Michael