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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

how to do data validation in excel 1
Excel ALERT a duplicate of the match of two cells in two different columns... please 0
Excel 4 characters alphanumeric data validation check on one excel column. 1
filter column unique for alphanumeric 3
Counting Unique Alpahnumeric's in a list 1
How to seperate ALPHANUMERIC data in a cell in an excel sheet. 3
Highest alphanumeric 6
automated cut and paste using regex 2

Back
Top