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

deronbalch

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

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. 

deronbalch

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

Mike Wilday

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

deronbalch

=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

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

deronbalch

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?

Mike Wilday

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

deronbalch

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

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

william.fx.bevilacqua14886

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

Mike Wilday

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

Replace my Year formula with your own... 

See screenshot for how it's working. 

stephanie.womack96516

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

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