macro for address

[vince cash]

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.

 

[Ron Rosenfeld]

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.

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