Formulas- Pulling dollar values from text string

MikeT

Hello,

I'am new to Smartsheets and trying to pull a dollar value out of a text string to put in an adjacent column. 

 

ACI - A/CP-BP 10k Type 2 Bullet Probe | 24" Leads $6.02     

Shaine Greenwood

Hi Mike,

If you're sure that you'll always have only one $ symbol in your dropdown field, you can use a formula like this:

=VALUE(RIGHT([Parts]1, FIND("$", [Parts]1)))

This formula will FIND the $ symbol and return characters from the RIGHT leading up to it as a numeric VALUE. Details on functions can be found in our Help Center: https://help.smartsheet.com/articles/775363-using-formulas

You'll then want to format your Price column for currency using the left toolbar. More information on formatting options can be found here: https://help.smartsheet.com/articles/518246-formatting-options

MikeT

Thank you for the response.

I copied and pasted that formula ......no luck.

J. Craig Williams

That is because there is an error.

=VALUE(RIGHT(Parts1, LEN(Parts1) - FIND("$", Parts1)))

FIND returns a number (like 53) and RIGHT takes that many characters from the right.

But you want only 3 from that starting point -- so LEN(string) - FIND results gets you what you need.

 Be aware that VALUE will turn 6.00 into 6 unless you have currency or decimals on. Set the column to be $ currency and you should be fine.

Also, be aware that this formula points to row 1. Change to your row number before copy/pasting elsewhere.

Craig

 

MikeT

Craig......Thank You..... It worked wonderfully..... "This gains you hero status for the week...use it wisely."     Mike

J. Craig Williams

You are welcome.

I'll try. 

Craig