Formula for Cents In Google Sheets

Hi

I would like to create a formula in Google Sheets, in one whole column.

I have a currency field that I would like to place the decimal in cents i.e:
$0.05

I would also like to include:
$0.01

Right now the format is:

05
(with no decimal place)

I tried to add the decimal as an option in both Airtable and Google Sheets, but it formats it into dollars. I want it in cents, and also dollars, too. I have over 200 cells/records, so I don’t want to have to do this manually, but rather automatically, either by a formula or another method.

I appreciate any help.

Thank you,
Mary

Um, {Cents}/100 = {Dollars}?

I’m not quite sure where the format mismatch is occurring. If you need to pass it as a text string to Sheets — ‘'$0.05'’ — then there’s code in my pretty-print routines you can snag. Or if you already have the cents’ values stored as a two-character numeric string (which ‘05’ would imply), you could simply concatenate: '$0.'&{Cents}. That might be enough to kick Sheets into storing it as a currency value…

Or am I barking up a totally wrong tree?

Hi @W_Vann_Hall

Thank you for your reply.

No, you are not barking up the wrong tree.

It’s the formula that I need, not the mathematical version or text format.

I will try the formula and hope for the best.

It’s great to see you back.

Take care.

Thank you for your help.

Mary

P.S. Will this formula work in one column in Google Sheets?

Hi @W_Vann_Hall

I wondered if there might be a formula for the cents, similar for GS, but for Airtable. I thought I would try either one or the other approach.

Thank you,
Mary

Mary -

Sorry – that was the Airtable formula; I thought maybe adding a calculated text field to the base and exporting it rather than your current {Cents} field to Google Sheets might cause it to interpret the currency value in cents rather than dollars…

Hi @W_Vann_Hall

Thank you for your help with the cents formula.

I set up the formula field, but I am noticing that the output doesn’t quite look right. I have attached a screenshot to illustrate the situation better. Although, I think I should have also mentioned that the formula needs to cover dollars, too.

Formula:
‘$0.’&{FACE VALUE}’

Thank you,
Mary

OK, it seems like your {Cents} field is (a) numeric and (b) can be higher than 99. In that case, try this:

IF(
    {Cents},
    '$'&
      INT(
          {Cents}/100
          )&
      '.'&
      REPT(
          '0',
          2-LEN(
              MOD(
                  {Cents},
                  100
                  )&''
              )
          )&
      MOD(
          {Cents},
          100
          )
    )

If you need to get fancier (not for your current base, but later on), there’s always this.