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.

Extract a number from the Left of a cell - HELP!

edited 12/09/19 in Archived 2017 Posts

Good Day to ALL.

I am new to the vommunity and need some help....

I have a survey form for my clients and when the results are populate, I would like to tally the results - by client.

What I am looking to do is extract the number to the left of the survey response.

eg. 5 - Very Satisfied

      4 - Somewhat satisfied

      3 - Neutral

I only want the numeric values - "5,4,3" etc. and have them SUM in a separate cell...

Any help is greatly appreciated.



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You can use the following formula in a new column. 

    =SUM(VALUE(LEFT([Column1]4, 1)), VALUE(LEFT([Column2]4, 1)))

    Use the brackets [   ] if your column name contains white space or numbers at the end. Basically, you are taking the leftmost character from the text value, and converting it to a number and summing them. Hope this helps. 

  • Mike,

    Thanks for the help. I think I am almost there.

    I have attached a screenshot of my sheet. I am needing to sum the numeric values of the 6 cells and populate the result in the Rating Column.

    I hope the screen shot helps.



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Can you please post what you have as a formula in the Rating column? 

  • =SUM(VALUE(LEFT([Price satisfaction]7, 1)), VALUE(LEFT([Transit timing satisfaction]7, VALUE(LEFT([EE to Client feedback]7, VALUE(LEFT([Communication satisfaction]7, 1)))))))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Try deleting all of the closing paragraphs at the end and posting it. Smartsheets will automatically close it for you. 

  • Thanks Mike. I tried the below:

    =SUM(VALUE(LEFT([Price satisfaction], 1)), VALUE(LEFT([Transit timing satisfaction], VALUE(LEFT([EE to Client feedback], VALUE(LEFT([Communication satisfaction], 1)

    and still get:



    Any Ideas?

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    edited 09/07/17

    Okay, I think I see the issue. Each of the formulas nested in the SUM formula were not properly closed. And you were missing specific row numbers on two of them. Try this rendition! :) 

    =SUM(VALUE(LEFT([Price satisfaction], 1)), VALUE(LEFT([Transit timing satisfaction]1)), VALUE(LEFT([EE to Client feedback]1)), VALUE(LEFT([Communication satisfaction], 1)))

  • Thanks!!!

    I used this minor edit and got it working.

    Thank a million!

    =SUM(VALUE(LEFT([Price satisfaction]1, 1)), VALUE(LEFT([Transit timing satisfaction]1, 1)), VALUE(LEFT([EE to Client feedback]1, 1)), VALUE(LEFT([Communication satisfaction]1, 1)), VALUE(LEFT([Our responsive nature]1, 1)), VALUE(LEFT([Likely to refer]1, 1)))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You're welcome. Glad I could help you out. 

  • How would you delete characters in a cell? I have a cell with the Year formula and want to drop the 20 from say 2018 and just leave the 18. Is there a way to that?


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Yes. =RIGHT(YEAR([Start Date]2), 2)

    Replace my Year formula with your own... 

    See screenshot for how it's working. 


  • This is an old post, so I'm hoping someone will see this question. I copied the working formula from the "deronbalch" response and SS keeps replacing the [ ] with ( ) and returning an error. Example: SS will 'convert' [Impact] to (Impact). What am I missing here?


    =SUM(VALUE(LEFT([Total Expenditures]1, 2)),VALUE(LEFT([Impact]1, 2)), VALUE(LEFT([Resources]1, 2)), VALUE(LEFT([Duration]1, 2)), VALUE(LEFT([Innovation]1, 2)), VALUE(LEFT([Schedule Risk]1, 2), VALUE(LEFT([Budget Risk]1, 2), VALUE(LEFT([Quality Risk]1, 2), VALUE(LEFT([Data Sensitivity]1, 2)))

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You only need to add brackets around column names that have spaces or end in numbers. Remove the brackets from Impact, Duration and Innovation.

This discussion has been closed.