I have this formula and it works well however I need the final result need to be a number. When I in

Mary Kam
Mary Kam ✭✭✭✭
edited 12/04/23 in Formulas and Functions

I have this formula and it works well however I need the final result to be a number. It needs to be a number because the index match does not work when I am comparing this value. I tried to add value in front of the formula and it gives me invalid value. I prefer not to to add another column to resolve this problem.


=IF(CONTAINS("/", [ORIGINAL STUDY NO.]@row), RIGHT([ORIGINAL STUDY NO.]@row, LEN([ORIGINAL STUDY NO.]@row) - FIND("/", [ORIGINAL STUDY NO.]@row)), IF(CONTAINS("/", [STUDY NUMBER]@row), RIGHT([STUDY NUMBER]@row, LEN([STUDY NUMBER]@row) - FIND("/", [STUDY NUMBER]@row)), ""))

Best Answer

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Answer ✓

    Hello,

    Try: =IF(CONTAINS("/", [ORIGINAL STUDY NO.]@row), VALUE(RIGHT([ORIGINAL STUDY NO.]@row, LEN([ORIGINAL STUDY NO.]@row) - FIND("/", [ORIGINAL STUDY NO.]@row))), IF(CONTAINS("/", [STUDY NUMBER]@row), VALUE(RIGHT([STUDY NUMBER]@row, LEN([STUDY NUMBER]@row) - FIND("/", [STUDY NUMBER]@row))), ""))

    Hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

Answers

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭
    Answer ✓

    Hello,

    Try: =IF(CONTAINS("/", [ORIGINAL STUDY NO.]@row), VALUE(RIGHT([ORIGINAL STUDY NO.]@row, LEN([ORIGINAL STUDY NO.]@row) - FIND("/", [ORIGINAL STUDY NO.]@row))), IF(CONTAINS("/", [STUDY NUMBER]@row), VALUE(RIGHT([STUDY NUMBER]@row, LEN([STUDY NUMBER]@row) - FIND("/", [STUDY NUMBER]@row))), ""))

    Hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

  • Mary Kam
    Mary Kam ✭✭✭✭

    Thanks for you help. It works

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!