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

  • Thread starter Thread starter DC
  • Start date Start date
D

DC

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


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

Lars-Åke
 
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
 
Back
Top