Summing the numbers in a cell that conyains text

K

klatlap

Hi...i need to add the numbers in several cells but the cells als
contain text....how can i sum these numbers with out deleting the tex
firs
 
F

Frank Kabel

Hi
could you provide some examples of your data. e.g.
- is the number in the middle, the beginning or end of this cell
- fixed position of the numeric part
- etc.
 
K

klatlap

this is thew format of each cell
5.2L
6.2L
8.5L
i need to sum the cells but the letter L stops me from doing thi
 
D

Dave Peterson

The alphabetic character is always the last character?

If yes, then this worked ok for me:

=SUM(--(LEFT(A1:A3,LEN(A1:A3)-1)))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
 
Top