how do I sum columns with cells that contain alphanumeric data?
 

Can anyone assist me in summing columns that have alphanumeric data, where
the data ends with m2? Thanks.

how do I sum columns with cells that contain alphanumeric data?
 

On Tue, 12 Aug 2008 11:53:01 -0700, DC
wrote:

Can anyone assist me in summing columns that have alphanumeric data, where
the data ends with m2? Thanks.


Please show a typical example of your input and the expected output.

Lars-Åke

how do I sum columns with cells that contain alphanumeric data?
 

hm,

When you say the data ends in M2 i assume it's the numbers to the left of
the M you want to sum. If so try this

=SUM(VALUE(LEFT(A1:A10,FIND("m",A1:A10)-1)))

Which is an array so commit with Ctrl+Shift+Enter NOT by pressing enter. If
you do it correctly then Excel will put curly brackets {} around it. You cant
type this yourself.


If its the numbers to the right of the M then use this. Once again an array.

=SUM(VALUE(MID(A1:A10,FIND("m",A1:A10)+1,LEN(A1))) )

Mike


"DC" wrote:

Can anyone assist me in summing columns that have alphanumeric data, where
the data ends with m2? Thanks.