# How can I sum the first character of a column in excel?

My column has data such as:

3 DAYS
5 DAYS
8 DAYS

I'd like to sum the number of days.

I can do `LEFT(B2, 1)` for example to get the number, however I am not sure how to combine that with `SUM` to create a total.

Use SUMPRODUCT to wrap your LEFT formula:

``````=SUMPRODUCT(--(LEFT(A1:A3,1)))
``````

The `--` does the same as `VALUE()`. It turns the text value that is returned by the LEFT() function into a number. • Note that this will return unexpected behavior if you ever have >9 days. It may be safer to instead sum everything to the left of the first space, using something like =SUMPRODUCT(--(LEFT(A1:A4,FIND(" ",A1:A4,1)-1))). Still, +1.
– Joe
Dec 12 '16 at 20:52
• =SUM(VALUE(SUBSTITUTE(A1:A3,"Days","",1))) use this formula as array formula press Ctrl+Shift+Enter and whatever numbers you it will sum it but Days should be the same
– yass
Dec 12 '16 at 21:28
• Nifty, thanks. My example was rubbish as the maximum would be 7 since it's for a week - but something to bear in mind for next time! Dec 13 '16 at 3:30

Click on data option in toolbar and select text to columns

Then delimit the columns by space ,then the string is separated from numerical

Now you can sum the column