Formula Question

Options

I have a list of items for sale in (column A) in a dropdown list to be used in a webform....

IE:

Dress $25

Pants $50

Shirt $30

I want to have the total cost of the selected item (single select in the column) populated (dollar amount only) in Column B automatically.

If Item 1 (Dress $25) is selected in Column A

THEN "$25" is populated in Column B (and can be used in a simple math formula at the end of the row).

Can someone help me with this basic formula?

Tags:

Answers

  • Lucas Rayala
    Lucas Rayala ✭✭✭✭✭✭
    Options

    Hi @KT Remus, first thing is to make sure Column B is a Text/Number column type in the column properties. Then select the column and then select the dollar sign (currency format) in the ribbon:

    If the values are always two digits (i.e. between 10-99), then your formula in Column B can be:

    =VALUE(RIGHT([Column A]@row, 2))

    That will give you a value that appears as a dollar and can be used as a number for any math or formulas.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @KT Remus

    The formula below uses the dollar sign as the 'anchor' to find your item price. This allows for the price to vary in length, say $1 or $150.50

    =VALUE(MID([Column A]@row, FIND("$", [Column A]@row) + 1, LEN([Column A]@row) - FIND("$", [Column A]@row)))

    Will this work for you?

    Kelly

  • KT Remus
    Options

    WOW - THAT WORKED... I have no idea how that works or why, but it was PERFECT!!! THANK YOU KELLY!

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Glad to help. In terms of how it works, the MID() function is doing the heavy lifting. The only thing you really need to remember is the dollar sign must be next to the numbers so that the numbers can be found.

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!