Clean and convert text value into currency value

Topic Labels: Formulas
1617 2
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hi there!

I have an AirTable sheet where I am collecting data from a form colleagues use. One field in this form is monetary values, but I’ve switch the receiving column to a text format because users don’t always enter integers only and I have no way to constrain the field in the form. Values can be ‘$100’, ‘100USD’, ‘100 USD’ when what I need is ‘100’.

So what I’d like to do is still receive the data in text format column that accepts all entries, and then grag this data, cleanse it, and keep only the numbers, to have an end result that’s an integer. So ‘100USD’ would become ‘100’.

I’ve looked at formula documentation but I can’t figure out what to do, would anyone have an idea that could help me please? Thanks!

2 Replies 2

Are all of your values going to be USD? If so, you could just use a currency field instead of a text field.

If using the Currency field won’t work, then try the VALUE() function to extract just the numbers out of the field.