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!
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.
Comments

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.
Thanks

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)))))))

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:
#UNPARSEABLE
Any Ideas?

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)))

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?
Bill

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)))

You only need to add brackets around column names that have spaces or end in numbers. Remove the brackets from Impact, Duration and Innovation.
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 220 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives