Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Convert mixed text to number

67 1
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a "rate" field that comes in in different formats so airtable (and excel) treat it as long text.

In excel I convert it to a number with this formula:  

=IFERROR(VALUE(IF(ISNUMBER([@Rate]),[@Rate],LEFT([@Rate],FIND("$",[@Rate])+4))),"")

Because the field can be:

1) A normal number = keep as is

2) Or something like this:  

"$ 0.00

Setup Fee $ 995.00" - where I am trying to strip out the spaces and "Set-Up Fee..." language.

Need help with a formula to do this in Airtable if possible.  ISNUMBER doesn't exist because the field type is set as text, etc.  I'm wondering if I need to use scripting...but that would be a first for me.

Thanks.

1 Reply 1

I think I figured it out.  This seems to work:  

 

VALUE(LEFT(Rate,8))