Split City State and Zip Based on Comma

[Amerifax-Bob]

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

 

 

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

 

 

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

 

 

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

 

 

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

 

 

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