#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Formulas- Pulling dollar values from text string

edited 12/09/19

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

Tags:

• Employee

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.

• ✭✭✭✭✭✭
edited 05/04/17

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.

Craig

This discussion has been closed.