Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
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
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
Thank you for the response.
I copied and pasted that formula ......no luck.
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
Craig......Thank You..... It worked wonderfully..... "This gains you hero status for the week...use it wisely." Mike
You are welcome.
I'll try.
