pull numbers from text string
 

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

pull numbers from text string
 

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

pull numbers from text string
 

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

pull numbers from text string
 

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

pull numbers from text string
 

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

pull numbers from text string
 

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

Thanks for your help! Works well.


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

pull numbers from text string
 

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

pull numbers from text string
 

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