Help

Re: LEN formula to currency

Solved
Jump to Solution
2911 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Julien_Etoke
6 - Interface Innovator
6 - Interface Innovator

Hello!

I automatically retrieve a list of sales directly in Airtable via Zapier, problematic, the price is sent "raw" from zapier with a lot of "0", like that : 16000 for 160

So I created a formula to remove them :

LEFT(raw_price, LEN(raw_price) - 2) 

And it works 🙂

Now, I am looking to create a new formula, in a new field to get the currency and be able to add the sales.

Any idea ? 🙂

1 Solution

Accepted Solutions

Ah good catch @Sho

@Julien_Etoke change the formula to

VALUE(LEFT(raw_price, LEN(raw_price) - 2))

to convert it to a number so that the currency option appears 😄

See Solution in Thread

11 Replies 11
Sho
11 - Venus
11 - Venus

Hm, do you mean like with a "$" or some such currency symbol?  Would formatting the field help?

Hi @Julien_Etoke,

You can adjust the formatting of a formula field to display a currency symbol of your choice:
formatting.png

Hope that helps!
-Stephen

Julien_Etoke
6 - Interface Innovator
6 - Interface Innovator

Hello!

I tried your differents solutions/ideas but I get an error. I will explain more precisely the idea :

Field 1 - "Sales" - (text) : Getting sales list from Systeme.io with too many "0", like that "16000" instead of "160"
Field 2 - "Sales without 0" - (formula) LEFT(Sales,LEN(Sales) - 2 : To delete the "00"
Field 3 - "Sales with currency" (formula) : I am trying to get the sales prices with currency to be able to summarize it in a second time.

On my field 3, I tried "
FLOOR({Montant clarifié}/100,1)"

Any idea ? 🙂


Hi @Julien_Etoke,

Have you tried the formula field’s formatting settings in the screenshot I posted earlier? You can make the output of your formula fields display as currency this way. Please let me know if it doesn’t work.

-Stephen

Julien_Etoke
6 - Interface Innovator
6 - Interface Innovator

Hello!

I created a new table to to clarify :

Price.JPG

I'm looking to get this result : 200 €

I tried this formula "FLOOR({field_name}/100,1)" on the field "Price + €" but I get an error 🙂

FLOOR is unnecessary as you mentioned the last 2 digits in the original value represented cents and then you removed them which would give you whole numbers as currency and disregard the cents. In field 3, why are you dividing by 100? To output in currency format, please see my previous comments.

I can't output with currency and I think i'm really lost to get what I am lookinf for 😃

Cur.JPG

Strings and numeric values are different things to a computer
To use a currency format, it must be numeric

Use the VALUE(string) function to force the conversion of strings to numeric values for calculations