You may have noticed that during certain processes, transaction/sale numbers are exported in their raw, unformatted form without decimal points. This can present problems to those who want to see their Transaction CSV files in an easy-to-read dollars-and-cents format, or send webhooks data to other programs (for instance, to make a receipt, record sales, or perform calculations). This article covers how to transform that data into your preferred form using Excel and Zapier.
Using Excel to Format Your Transaction CSV
When you download your Transactions CSV, you’ll receive a spreadsheet that can be opened with Excel. This process makes it easy to format numbers into dollars and cents (without having to add decimals one by one).
- When you first open your spreadsheet, it will look something like this, with numbers in their raw format (counted in cents):
- Find an open cell (it doesn’t matter where) and type in the number 100.
- Right-click on your cell and copy it.
- Click on one of the columns you’d like to format in dollars and cents. All the cells in that column should be highlighted when you do this.
- Now, we’re going to use a special paste action to divide all of these numbers by 100. Right-click on your highlighted column and find Paste Special. The action we want isn’t immediately visible here, so we’ll need to go into the secondary Paste Special area under Other Paste Options.
- Under Operation, select Divide. Click OK to complete the paste action and divide all the numbers in the column.
- Repeat with the other columns you want to format. (You can select multiple columns at once to save even more time, by using CTRL or by clicking, holding, and dragging across adjacent columns.) Your final product should look like this:
Now, you can easily read and use your CSV to understand or process transaction data.
Setting Up Zapier to Export Formatted Numbers
With Zapier, you can set up a zap that will automatically convert new sale data into your desired dollars-and-cents format before it continues on to another program (e.g. Quickbooks). The basic premise is that you can divide the sale numbers by 100 before they are exported by Zapier. Follow these steps to set up your zap.
- Log into your Zapier account and click Make A Zap!
- Choose Teachable as your trigger app.
- Select New Sale as the Teachable trigger.
- Choose what Teachable account/school to apply the Zap to. If you haven’t connected one yet, you’ll be prompted to do so.
- Now, Zapier will perform a test, using a previous sale to get the data it needs for the next step.
- Your Trigger step is done, so now move on to the Action step. In the Choose an Action App section, search for Formatter by Zapier and select it.
- In the Choose Action section, select Numbers.
- Moving on to the Template step, search for Perform Math Operation in the Transform field and select it.
- In the Operation field, select Divide. Then, in Input, put Final Price as Step 1. Add 100 on another line below that.
- If you have other fields that you would like to reformat, such as author or affiliate fees or your earnings after fees are applied, you can search for those and add them in the same Step 1 field. Click Continue when you’re done.
- Zapier will conduct another test to make sure the zap works successfully, and let you know if it does.
- At the end of this process, you’ll be taken to a page where you can turn your zap on.
You’re done! Now, whenever a new sale occurs, the data will be sent to your connected programs in the desired format.