Formatting Transaction Numbers With Excel and Zapier

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).

  1. When you first open your spreadsheet, it will look something like this, with numbers in their raw format (counted in cents): raw numbers in excel spreadsheet
  2. Find an open cell (it doesn’t matter where) and type in the number 100. type 100
  3. Right-click on your cell and copy it. copy cell
  4. 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. select cells to format
  5. 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 Optionspaste special
  6. Under Operation, select Divide. Click OK to complete the paste action and divide all the numbers in the column.divide
  7. 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: final excel spreadsheet

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.

  1. Log into your Zapier account and click Make A Zap!
  2. Choose Teachable as your trigger app. select teachable as trigger
  3. Select New Sale as the Teachable trigger. new sale
  4. Choose what Teachable account/school to apply the Zap to. If you haven’t connected one yet, you’ll be prompted to do so. connect teachable account
  5. Now, Zapier will perform a test, using a previous sale to get the data it needs for the next step. test teachable trigger
  6. 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. formatter action app
  7. In the Choose Action section, select Numbersnumbers
  8. Moving on to the Template step, search for Perform Math Operation in the Transform field and select it. perform math operation
  9. In the Operation field, select Divide. Then, in Input, put Final Price as Step 1. Add 100 on another line below that. set up math operation formatter
  10. 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. reformat other fields Click Continue when you’re done. 
  11. Zapier will conduct another test to make sure the zap works successfully, and let you know if it does. test formatter
  12. At the end of this process, you’ll be taken to a page where you can turn your zap on. finish formatter zap

You’re done! Now, whenever a new sale occurs, the data will be sent to your connected programs in the desired format.

thanks_illustration

Can't find what you're looking for?

Contact us
thanks_illustration