HomeGoogle DocsSpreadsheetSum Cells With Numbers and Text in a Column in Google Sheets

Sum Cells With Numbers and Text in a Column in Google Sheets

Published on

Google Sheets considers numbers mixed with text as text only. So, typically, you cannot sum cells with numbers and text in a column in Google Sheets.

If your data entry operator is new to the job, you may encounter situations that test your data manipulation skills. An inexperienced Data Entry Operator (DEO) can cause the following common errors in your spreadsheet.

A common issue you may encounter in your spreadsheet is a column/row or an array that has numbers mixed with text (alphanumeric characters). Usually, you may come across such entries in columns with currency symbols as well as units of measurement.

How do we sum a column with numbers and numbers mixed with text in Google Sheets? Let’s delve into the details.

Error When Summing Cells Containing Numbers and Text in a Column

Can I use the SUM function to sum the values in column C in the following sample data set?

Example to sum cells with numbers and text in Sheets

No, you can’t use the SUM function to sum a column with mixed content as shown below:

=SUM(C2:C6)

The above formula will return 0 because there are no purely numeric values in column C. In column C, all the numbers are mixed with units of measurement, represented as strings/text.

In Google Sheets, to handle such columns with mixed content in calculations, you should first remove the textual parts from the numbers.

Learn how to remove text from numbers in a column and then sum in Google Sheets. It’s not a complicated process.

How to Sum Cells With Numbers and Text in a Column in Google Sheets

Here is the formula to sum a column with mixed content in cells, utilizing Google Sheets functions such as SUM, IFERROR, ArrayFormula, SPLIT, and REGEXREPLACE.

Formula #1 (for a Column):

=ArrayFormula(SUM(IFERROR(SPLIT(REGEXREPLACE(C2:C&"", "[^\d\.]+", "|"), "|"))))

Formula Explanation

We are dealing with an array/range, so we must use the ArrayFormula with other non-array functions in this formula. Now let’s start with the formula explanation step-by-step.

The REGEXREPLACE function replaces all the texts in column C with the pipe “|” symbols.

=ArrayFormula(REGEXREPLACE(C2:C&"", "[^\d\.]+", "|"))
VALUE error in Regex in mixed and alphanumeric column data

Do you know why I have used C2:C&"" instead of C2:C?

This is to convert numbers within the range to text strings. Otherwise, if numbers are present in column C, the formula would return a VALUE! error as the REGEXREPLACE function expects text values in parameter 1.

The SPLIT function splits the above result based on the “|” as the delimiter.

=ArrayFormula(SPLIT(REGEXREPLACE(C2:C&"", "[^\d\.]+", "|"), "|"))

The SPLIT function is not required if you use "" instead of the Pipe symbol in REGEXREPLACE.

I opted to go with the Pipe symbol as sometimes any of the cells may contain multiple numbers as follows: 350 MT, 450 MT

In such a case, if you use "" instead of |, REGEXREPLACE will return this as one whole number like 350450. I want a separator between like 350 | 450 so that I can split it into two separate numbers.

The IFERROR is to remove any other errors associated with blank cells in the column.

=ArrayFormula(IFERROR(SPLIT(REGEXREPLACE(C2:C&"", "[^\d\.]+", "|"), "|")))
Regex formula to extract numbers from column

Finally, the SUM function sums all the values.

As per our example, if you remove the SUM function from the beginning of the formula, you can use it to extract only numbers from the column. Thus, it can be useful for cleaning your Google Sheets data.

You can use my formula in any column as it’s an array formula. That means this formula can sum an entire column that contains alphanumeric characters. You only need to modify the cell references in this formula to work.

Sum Alphanumeric Characters in a Row in Google Sheets

Can I use the above formula in a row?

Why not? With my formula, you can not only sum cells with numbers and text in a column but also a row. However, you need to tweak the formula a little bit.

If the numbers with text (alphanumeric characters) are in C2:G2, instead of using C2:G2&"", use TRANSPOSE(C2:G2)&"".

Formula #2 (for a Row):

=ArrayFormula(SUM(IFERROR(SPLIT(REGEXREPLACE(TRANSPOSE(C2:G2)&"", "[^\d\.]+", "|"), "|"))))

Here, use the TRANSPOSE function to transpose the row to a column.

Summing Alphanumeric Characters in a 2D Array

To sum alphanumeric characters (cells with numbers and text) in a 2D array, you should tweak the formula #1.

In the following example, I need to sum cells with numbers and text in B2:F3, a 2D array. For that, we need to convert the values into a single column and use our earlier formula.

That means you need to replace C2:C in our formula #1 with TOCOL(B2:F3); the TOCOL function transforms the range of cells into a single column.

Formula #3 (for a 2D Array):

=ArrayFormula(SUM(IFERROR(SPLIT(REGEXREPLACE(TOCOL(B2:F3)&"", "[^\d\.]+", "|"), "|"))))

Note: You can use TEXTJOIN instead of TOCOL.

Sum Cells With Numbers and Text in Multiple Columns

Hope you have learned how to sum a column where numbers are mixed with text in Google Sheets.

Resources

We have used REGEXREPLACE to remove text and facilitate the summation of cells with numbers and text in Google Sheets. Here are some related topics discussing scenarios that involve text, numbers, and summation.

  1. Sum Text Values Based on Assigned Scores in Google Sheets
  2. Using SUMIF in a Text and Number Column in Google Sheets
  3. Extract All Numbers from the Text and SUM It in Google Sheets
  4. Add Custom Text to Numbers in Google Sheets (with Calculation Support)
Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

36 COMMENTS

  1. Hello there. I need to be able to sum numbers containing decimals. For example, values “2” and “0.5” should sum up to 2.5 instead of 7. How could I achieve that?

    Thanks for your help!

  2. Hi Prashanth,

    Thanks for sharing this.

    I need to know if there is a way, to sum up only cells that match a criterion.

    —URL removed by Admin—

    Any help would be very appreciated!

    • Hi, Mike,

      Please try this formula yourself. I don’t have editable rights to your Sheet.

      Replace the range D4:K4 in your formula with if(search("(s)",D4:K4),D4:K4).

      So the formula will include the cells containing an “(s)” in them.

      Further, you can make a single formula for the whole column. For that, empty the L4:L47 range and insert the following formula in L4.

      =byrow(D4:K47,lambda(r,ArrayFormula(SUM(IFERROR(SPLIT(
      REGEXREPLACE(TRANSPOSE(if(search("(s)",r),r))&"",
      "[^\d\.]+", "|"), "|"))))))

  3. Dear Prashanth,

    Thank you for your simplified solutions

    I want to use your Formula # 3 on my sheet on which data is submitted from a Google Form, and I need to automate the results.

    Kindly help with this.

    Thank you.

  4. Having trouble finding a way to do something similar but not quite this.

    On my Master Sheet, I am using Vlookup to find the data from a sheet called Classes.

    I’m trying to add two data pulls in one cell. However, it’s not adding correctly or not at all.

    So, using J1 and K1 for criteria 1, and L1 and M1 for criteria 2.

    The drop-down list in L is set to Class if not used.

    The column I’m pulling from has either single numbers or 2 or 3, or 4 numbers separated by a slash ex. +1, +6/+1, +11/+6/+1, +20/+15/+10/+5.

    By the way, I had to set that column to text so they wouldn’t auto transform to dates.

    I’m trying to have it added together. So that using those examples, it would read +38/+22/+11/+5 in one cell.

    Using;

    =if($L$1="Class",vlookup($J$1&" "&$K$1,Classes!$B$2:$D,2,false),
    vlookup($J$1&" "&$K$1,Classes!$B$2:$D,2,false)+vlookup($L$1&" "&$M$1,
    Classes!$B$2:$D,2,false))

    It works if L1 is Class or the numbers are all single numbers.

    Any advice?

  5. Hi Prashanth,

    I have table in sheet with data U2, U, A, U1 in rows. Is it possible to get data sum U = 6 (U + 2 + U + U + 1)
    How to create a formula with this case? I think, need to update the regex expression, but I haven’t been able to find a way.

    Really appreciate it if you can help me.

    Best,
    Imam

  6. I have a similar question to Ella from above. Firstly, thank you for your explanations. They’re great.

    I have cells with numbers, letters, commas, and decimals.

    This formula splits that into two different numbers. So I’m trying to figure out how to accommodate that using the SPLIT.

    I’ve made a test version of the Sheet here so you can see what I’m talking about.

    — link removed by admin —

    • Hi, Tyler,

      You have ‘Miles’ in column range E8:E

      E.g.:-

      1,522 mi
      1.0 mi


      You can use the below array formula in cell F7 (first, empty F7:F).

      ={"Miles";ArrayFormula(if(E8:E="",,IFERROR(SPLIT(REGEXREPLACE(E8:E&"", "[^\d\.\,]+", "|"), "|"))))}

      Or the below shorter version.

      ={"Miles";ArrayFormula(if(E8:E="",,value(REGEXREPLACE(E8:E&"", "[^\d\.\,]+", ""))))}

  7. Hi Prashanth,

    Is it possible to create a formula to sum numbers within a single cell that contains both commas and decimal points?

    Best,
    Ella

  8. How would I do this with subtraction instead of addition? Trying to get the difference between the G column and the F column. I tried using formula # 2 but changed “Sum” to “Minus” and resulted in an error asking for “2 arguments”. Pretty new to google sheets so no clue where to go from here.

    Thanks!

    • Hi, Daniel,

      I wish to see an example. Send it, if possible.

      You can create a sample sheet and use the ‘Share’ button on the sheet to copy the link to share. I want ‘Edit’ or ‘View’ access.

      Best,

  9. Hi Prashanth.

    I have cells in rows containing data like:

    P1: 5min, $2; P2: 10min, $4
    P1: 15min, $56
    P1: 10min, $4; P2: 2min, $1; P3: 10min, $4; P4: 15min, $4

    Is there a way to use this formula to sum the dollar amounts only? If I use this formula:

    =ArrayFormula(SUM(IFERROR(SPLIT(REGEXREPLACE(TRANSPOSE(C2:G2)&"", "[^\d\.]+", "|"), "|"))))

    The results would be:
    24
    72
    60

    rather than:
    6
    56
    13

    I’d appreciate any help you can give!

      • Thanks, Prashanth. Do you know if there’s a way to sum for my case then? The data is in a row (from G:AD). Some cells have data, some do not and only have 0 in the cell. Of the cells with data, some have single values (e.g. P1: 2min, $1), some have multiple values (e.g. P1: 2min, $1; P2: 3min, $2). I’m hoping to be able to add up only the dollar amounts of a row.

        • Hi Emily,

          I will try my best on a sample Sheet. Copy your sheet and share it with me (EDIT access is required). Make sure you are not sharing any personal or sensitive info.

          To share the copied file, please follow this quick instruction.

          Click the green SHARE button > Get shareable link > Anyone with the link can edit > Copy link.

          Best,

      • Thanks. I had a look, but the formulas here apply only to single-cell references and still doesn’t work in my sheet.

        I really appreciate you taking the time!

        • Hi Emily,

          The value in cell B5 is P1: 23min, $2

          When the formula splits, it gets values in two cells as below.

          First value:

          P1: 23min,

          Second value:

          $2

          This second value is actually in the numeric format so that the Regex won’t work (which is a text function).

          So we need to convert these outputs to text. So I have included the To_Text additionally with my formula.

          =ArrayFormula(SUM(IFNA(REGEXEXTRACT(to_text(split(substitute(B5,"$","~$"),"~")), "\$([0-9.]+)")*1)))

          To sum numeric values prefixed by $ in the entire row, you can use the formula as follows.

          =ArrayFormula(SUM(IFNA(REGEXEXTRACT(to_text(split(substitute(TEXTJOIN(" ",true,B5:Z5),"$","~$"),"~")), "\$([0-9.]+)")*1)))

          Best,

  10. Is there any way that this will work with adding negative numbers? Right now the formula is adding everything together positively regardless of whether or not there’s a – in front of it. Thanks!

      • Prashanth,

        Total newbie here, so please tell me if I went wrong here, but when inputting the formula to work with a row, it will return a “array arguments to IF are of different size.” Do you have an explanation for this?

        TIA

        • Hi, Alex,

          Thanks for your feedback. I have updated my tutorial. Now it contains 3 formulas – the first one to use in a column, the second one for row and the third one to use in an array.

          The third formula is flexible. You can use that in a row, column or even multiple rows and columns.

  11. Hi, Thank you for the great formula. I only have one problem.

    If I select any cells that do not contain any text or number, it gives me an error #VALUE! Function SPLIT parameter 1 value should not be empty.

    I have a Sheet with mixed numbers and text. Your formula works if there is text in the highlighted cells.

    I want it to work with empty cells as well so later on anyone who enters the data in empty cells it automatically calculates the numbers, as of right now if I highlight cells with no text it gives me the error I mentioned above

    Thanks!

    • Hi, Dovah,

      I don’t find any such issue in my testing. Are you using the correct formula that contains the ISTEXT function?

      Even if there any such unforeseen error, you can overcome that by simply wrapping the formula with the IFERROR function.

LEAVE A REPLY

Please enter your comment!
Please enter your name here