How to sum all the numbers where there is a letter together with the numbers in the cell?

Patrikas

Hello,

I need some help adding all the hours in the range, but in each cell there will be a letter:

I need to get '20' without any letters. Is it possible to do? Thank you in advance!

Sing C

Hi @Patrikas, I'm sure others would have other suggestions. If the format of the values in your hours column is always the number + one letter, one way you could do it is create another column and use a formula like the below to "trim off" the final character - then sum the values n that column.

=LEFT([Column name]@row, LEN([Column name]@row) - 1)

Let me know if that helps!

Patrikas

https://community.smartsheet.com/discussion/comment/333126#Comment_333126

Hello @Sing C, many thanks for your insight. I'm afraid I will have a column for each day for upcoming 365 days, so do I need to make helper column for each day then? Let me know, cheers!

Sing C

Hi @Patrikas, apologies, I don't fully understand your comment. Do you have a screenshot you can share with column names and some sample data? Thanks!

Patrikas

https://community.smartsheet.com/discussion/comment/333270#Comment_333270

Sing,

yes, of course.

So basically it's a roster and we want to sum all hours on each day. For example, on september 1st, there is N12 and D12, which makes total 24hrs. Is it possible to sum for each day in the bottom? Cheers.

Sing C

Hi @Patrikas, you could. Do the number of rows vary? I see from the screenshot, there are potentially 4 rows that could have data in them. Looking at Sep 3rd, I see N11, N12, S and a blank row under that.

Assuming it is 4 rows and your first row of data that you want to sum is row 5 (it looks like row 5), try this formula in row 13 (the first row after the two yellow and one blue rows):

=SUM(VALUE(MID([Column Name]5, 2, LEN([Column Name]5))), VALUE(MID([Column Name]6, 2, LEN([Column Name]6))), VALUE(MID([Column Name]7, 2, LEN([Column Name]7))), VALUE(MID([Column Name]8, 2, LEN([Column Name]8))))

I've changed it a little because it looks like the letter comes before the number but your original post has the letter coming after the number.

Patrikas

https://community.smartsheet.com/discussion/comment/333287#Comment_333287

Hi Sing,

many thanks for your efford. I can assure that there will be 200+ rows in the future, and numbers will vary from 1 to 24. I'll try your formula!