Modifying Property Smarts Data

In this cheat sheet we're going to modify a CSV of Property Smarts property records.

In our direct mail campaign we're going to merge an expected sale price into the recipient's letter by adding 200k to their current value. 200k is the average premium over RV for our farming area.

1. Export a list of addresses and open in Excel.
   
2. Right-click insert an empty column next to the Capital Value column, then enter 200000 as text into the first cell.
   
3. Select the '200000' cell and right-click copy.
   
4. Select all of the CV cells and right click Paste Special -> Add
   

If the currency formatting is kept in its original style, the commas will break the sequence when imported into SalesPartner. The solution is to copy the column into Wordpad, then use Find & Replace to change commas into '^'. The next trick is to remove any surplus spacing using a tool like https://www.textcleanr.com/

Then copy the column back into Excel next to the original CV column so you can check that the layout is consistent.

Rebuilding commas explained:

Right click the column to reinstate accounting format (hide the symbol).

In Wordpad, use Find & Replace to switch commas to '^'.

Removing all spaces:

Paste the column back into Excel and check the top and bottom cells match.

Converting the ^ symbol back into commas during import using the dedicated string tool:

Result

Now we can merge the potential vendor's expected sale price into our prospecting letter: