Thread
:
pull numbers from text string
View Single Post
#
4
Posted to microsoft.public.excel.misc
[email protected]
[_2_]
external usenet poster
Posts: 2
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
Reply With Quote
[email protected]
[_2_]
View Public Profile
Find all posts by
[email protected]
[_2_]