Jump to content
Macro Express Forums

Split City State and Zip Based on Comma


Recommended Posts

I hope to help many with this instruction on how to deal with creating a variable based on this string form the ClipBoard:

 

John Smith

477S 18TH ST

OMAHA, NE 68107-2157

Phone: 402-346-6649

 

Listed Since: 08/2009

Residence

 

County: DOUGLAS (31055)

MSA: OMAHA, NE-IA (5920)

Latitude: 41.214360

Longitude: -95.939340

Precision: Geocoded to address precision

-------------------------------------------------------

Now it's payback time for all the great help I've gotten on this forum. I am going to detail start to finish how to take an address, such as city state zip and split apart and place it into Excel.

 

 

Here , this is the code regarding the variable:

 

MacroExpresss Pro V 2011.3.04.2300

 

1. This macro is based on captured text. The source that we are working with will not work with a simple capture. It first must be pasted into a text editor. Then recaptured before we can begin. This is a limitation created by the source program.

 

2. Assuming you have already captured your data. It's time to convert the clipboard to variables. Sample Code:

// Place Clipboard into Variable

Variable Set to ASCII Char 13 to %CR%

Variable Set to ASCII Char 10 to %LF%

Variable Set String %Capture% from the clipboard contents

Split String "%Capture%" on "%CR%%LF%" into %Line%, starting at 1

Delay: 185 milliseconds

 

Note: All values that are in between %???% are names that we give the variable

a. "ASCII Char 13" - System term for carriage return, not a ME term. It's a system term for carriage return We gave it our name "CR"

b. "ASCII Char 10" - System term for "Line Feed" or End of Lin

c. "Variable Set String" Create a variable called "Capture" using clipboard contents. See Picture called c-Variable Set String

This will take the text on the clipboard and split it into lines. You can see the results by going into the Menu "Debug - Show Variable Values." I many times asked if there was a way to see the clipboard, what I called the clouds. Well here it is...

 

C - Variable Set String

 

post-47-0-12407000-1305318695_thumb.png

 

d. Split String (setup %Line% Variables by splitting at %CR%%LF%) So setup a variable for each line in the clipboard.

 

post-47-0-80518200-1305318727_thumb.png

 

e. Split String to seperate the City, State and Zip Code

 

post-47-0-96575400-1305318753_thumb.png

 

First I split the line at the ", " So that the City is in variable %CitySt[1]% and the State Zip are in Variable %CitySt[2]%.

 

f. Split String to Seperate State and Zip Code

 

post-47-0-83275500-1305318781_thumb.png

 

Here I split the Variable %CitySt[2]% into 2 variables, at the double space, one for state and one for zip (%StateZip%).

 

g. Then to remove the headings from from my last few Variables such as (Phone: 262-222-2222), I used:

 

Variable Modify String

 

post-47-0-00682300-1305318819_thumb.png

 

Now it's time to place a variables into Excel. I feel this is pretty self-explanatory and needs no explanation. But don't hesitate to ask questions. If you need interpretation.

 

Delay: 185 milliseconds

Window Activate: Microsoft Excel - Book1

Wait for Window Title: Microsoft Excel - Book1

Delay: 185 milliseconds

Text Type (Simulate Keystrokes): %Line[1]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[2]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%CitySt[1]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%StateZip[1]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%StateZip[2]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[4]%

 

Here is the complete code used once the data is on the clipboard:

 

// Place Clipboard into Variable

Variable Set to ASCII Char 13 to %CR%

Variable Set to ASCII Char 10 to %LF%

Variable Set String %Capture% from the clipboard contents

Split String "%Capture%" on "%CR%%LF%" into %Line%, starting at 1

Delay: 185 milliseconds

// Split Variable for City, State Zip

Split String "%Line[3]%" on ", " into %CitySt%, starting at 1

Split String "%CitySt[2]%" on " " into %StateZip%, starting at 1

Variable Modify String %Line[4]%: Delete a substring starting at 1 and 7 characters long

Variable Modify String %Line[6]%: Delete a substring starting at 1 and 14 characters long

Variable Modify String %Line[11]%: Delete a substring starting at 1 and 10 characters long

Variable Modify String %Line[12]%: Delete a substring starting at 1 and 11 characters long

// Place values from SelectPhone into Excel

Delay: 185 milliseconds

Window Activate: Microsoft Excel - Book1

Wait for Window Title: Microsoft Excel - Book1

Delay: 185 milliseconds

Text Type (Simulate Keystrokes): %Line[1]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[2]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%CitySt[1]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%StateZip[1]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%StateZip[2]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[4]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[6]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[11]%

Text Type (Simulate Keystrokes): <ARROW DOWN>%Line[12]%<ENTER>

 

 

I hope I have helped. All the above is much research with the biggest dictionary being members of this forum. Most notably Kevin and Cory.

 

Bob

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...