This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Re: Formula for Cents In Google Sheets

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

0
2859
0

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 28, 2019 11:45 AM

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

8 Replies 8

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 28, 2019 11:58 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 28, 2019 12:04 PM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 29, 2019 11:46 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

May 29, 2019 01:20 PM

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…

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2019 04:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jun 07, 2019 05:39 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Jul 07, 2019 01:09 PM

Hi @W_Vann_Hall

I think I gave a follow-up to the cents formula too soon.

I created new records in Airtable using Integromat, from GS. I noticed that there was an added zero to the currency, in the formula field, in Airtable. My other field is a currency field. Could this formula work with a currency field and an adjustment to the formula or change the currency field to text and also, change the formula? Although, I would preferr to keep the currency field, since there are other sources that I will use data from, to create/update records in Airtable. It’s just this source of data that the currency is not formatted properly.

I should also mention that when I enter the currency, without decimals, directly into Airtable, the currency is formatted correctly. It’s when I have the data imported, through Integromat, using the currency without added decimals (source data), that I get the added zeros. Could it have to do with the currency format in GS? The GS format is at zero decimals and the cell format is automatic. That’s the only difference that I could think of, that’s different.

The currency field for Airtable is set at 2 decimal places with a dollar currency symbol and it allows for negative numbers.

I have attached screenshots to better explain the issue. Disregard the left side screen in the third screenshot.

Thank you for your help and patience.

Mary

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Feb 05, 2021 01:22 PM

Reply