MacMost Q&A Forum • View All Forum QuestionsAsk a Question

How Do I Total Numbers In a Row Of Cells That Also Contains Letters?

I have a row of cells that include letters and numbers. For example, one cell may contain “A 3” and another cell may contain “DG 2”. Is there a way to sum the numbers in those cells in another single cell? I’ve been attempting to do it with REGEX.EXTRACT per your tutorial where you extract the numbers only within a single cell. However, I’m hoping I can do it with multiple cells and then add the numbers up into a total value in another cell.

I’m trying to get score totals for a paired comparison analysis table where the options I’m comparing are labeled alphabetically and each comparison receives a score of 1-3. So if I compare option A to option B and I like option A better, I put A in the cell. If I like option A significantly more than option B I give it a score of 3 (so “A 3”) and if I like option A only slightly better than option B I give it a score of 1 (so “A 1”). A score of 2 is also possible. If I like both options equally I simply put a 0.
—–
Rocco

Comments: 2 Responses to “How Do I Total Numbers In a Row Of Cells That Also Contains Letters?”

    4 years ago

    To do this, create new columns after that special score column, and use REGEX.EXTRACT to break it apart. So if this is column D, then column E would contain the letter part, and column F would contain the number part. Then you can simply use SUM to get the total of F, or SUMIF to get the total of F where column E matches a specific letter.

    Rocco
    4 years ago

    That's perfect. Thanks Gary!

Comments Closed.