macro for address

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

vince cash - 13 Oct 2006 18:22 GMT

i want to move part of an address to another column,

711 W Fairground Rd #3
414 Chestnut St
109 Locust Grove Dr. Apt. 4
522 Folkstone Dr
2996 Polo Club Blvd apt 2
316 Princess Cir #6
2404 Shandon Dr

i need 711 w fairground rd to be in column G and in column F i need #3
same for 109 locust grove dr. needs to be in G and the APT 4 in F.

Reply to this Message

Ron Rosenfeld - 13 Oct 2006 19:02 GMT

>i want to move part of an address to another column,
>
[quoted text clipped - 8 lines]
>i need 711 w fairground rd to be in column G and in column F i need #3
>same for 109 locust grove dr. needs to be in G and the APT 4 in F.

We need to know the identifying features of the portion you want stripped out.
In your example, it appears as if that is represented by either the " #" and
everything following; or " Apt" and everything following, or " apt" and
everything following.

If that is the case, and if those values don't appear prior to the end, then
you could use a Regular Expression formula. First download and install
Longre's free morefunc.xll add-in from http://xcell05.free.fr/

Then, assuming your data is in A1,

G1: =REGEX.SUBSTITUTE(A1,"\s(#|Apt).*",,,,FALSE)
F1: =REGEX.MID(A1,"(#|Apt).*",,FALSE)

--ron

Reply to this Message

Rate this thread: