# LEN formula to currency

Topic Labels: Formulas
Solved
1601 11
cancel
Showing results for
Did you mean:
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
10 - Mercury

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 😄

11 Replies 11
11 - Venus
18 - Pluto

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

10 - Mercury

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

Hope that helps!
-Stephen

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 ? 🙂

10 - Mercury

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

6 - Interface Innovator

Hello!

I created a new table to to clarify :

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 🙂

10 - Mercury

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.

6 - Interface Innovator

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

11 - Venus

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