提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在Excel中對文本和數字的單元格進行求和?

例如,我有一個包含數字和文本字符串的值列表,現在,我只想根據相同的文本求和數字,請看下面的截圖。 通常情況下,您不能直接在文本列表中對列表中的值進行求和,在這裡,我將向您介紹一些處理此任務的公式。


將文本字符串拆分/分隔為單獨的文本和數字列:

Kutools for Excel拆分單元格 功能是一個強大的工具,它可以幫助您將單元格值拆分為多個列或行,它可以幫助您將字母數字字符串分割為單獨的文本和數字列等。

doc總和文本號8

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


根據具有公式的特定文本求和包含文本和數字的單元格


Office選項卡在Office中啟用選項卡式編輯和瀏覽,使您的工作更輕鬆......
Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%
  • 重用任何東西: 將最常用或最複雜的公式,圖表和其他任何內容添加到您的收藏夾中,並在將來快速重複使用它們。
  • 超過20文本功能: 從文本字符串中提取數字; 提取或刪除部分文字; 將數字和貨幣轉換為英文單詞。
  • 合併工具:將多個工作簿和工作表合二為一; 合併多個單元格/行/列,而不會丟失數據; 合併重複的行和總和。
  • 拆分工具:根據價值將數據分割成多個工作表; 一本工作簿可轉換為多個Excel,PDF或CSV文件; 一列到多列。
  • 粘貼跳過 隱藏/過濾行; 數和總和 按背景顏色; 將個性化電子郵件批量發送給多個收件人。
  • 超級過濾器: 創建高級過濾方案並應用於任何工作表; 分類 按週,日,頻率等; 過濾 通過大膽,公式,評論......
  • 超過300強大的功能; 與Office 2007-2019和365一起使用; 支持所有語言; 在您的企業或組織中輕鬆部署。

您可以使用數組公式來根據單元格中相應的文本字符串對數字進行求和,具體操作如下:

1. 首先你可以寫下你想要在列單元格中求和相對數字的文本字符串。

doc總和文本號2

2。 然後在空白單元格中輸入以下公式,在本例中,我將在單元格E1中輸入公式: =SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8)-1)),0)),然後按 Ctrl + Shift + Enter 鍵一起,它將基於特定文本從列表中返回總值。 看截圖:

doc總和文本號3

小技巧: :在上面的公式中, D1 是你想要使用的標準,而且 B2:B8 表示您想要求和的單元格範圍。

3。 然後選擇公式cel,然後將填充手柄向下拖動到要應用此公式的單元格以獲取結果:

doc總和文本號4


包含文本和數字的單元格忽略使用用戶定義函數的文本


如果您有以下與文本和數字混合的數據,並且您只想提取並添加每個單元格中的數字,那麼您如何快速在Excel中完成此操作?

doc總和文本號5

要處理這個問題,你可以創建一個 用戶自定義函數,請這樣做:

1。 按住 ALT + F11 鍵打開 Microsoft Visual Basic for Applications窗口.

2。 點擊 插入 > 模塊,並將以下代碼粘貼到 模塊窗口.

VBA代碼:僅匯總包含數字和文本的單元格中的數字

Function SumNumbers(rngS As Range, Optional strDelim As String = " ") As Double
'Update 20141210
    Dim xNums As Variant, lngNum As Long
    xNums = Split(rngS, strDelim)
    For lngNum = LBound(xNums) To UBound(xNums) Step 1
        SumNumbers = SumNumbers + Val(xNums(lngNum))
    Next lngNum
End Function

3。 然後保存並關閉此代碼,返回到工作表,並輸入此公式 = SumNumbers(A1) (A1 指示您想要總結數字的單元格),然後將填充手柄向下拖動到您要填充公式的單元格中,並且每個單元格中只有數字相加,請參閱截圖:

doc總和文本號6


將單元格內的所有單個數字相加

將單元格內的所有單個數字相加

如果你想把一個單元格內的所有數字相加, Kutools for Excel總和數字在一個單元格中 可以幫助您在一個單元格中快速匯總一個號碼的所有數字。

doc總和文本號7

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


相關文章:

如何在Excel中總結每n行?

如何根據Excel中的文本條件來求和值?


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Maarten · 2 days ago
    I have a variant of this problem you solved with the SumNumbers module. The issue is that the cell contains both dollar values and other numbers, for example:
    "Case of Soda (354mL x 24): USD 25.00 Case of Water (0.500L x 24): USD 15.00"

    Using the SumNumbers module adds up all numbers but we only want the dollar values.
  • To post as a guest, your comment is unpublished.
    Z · 22 days ago
    If you have the following data which is mixed with text and numbers, and you just want to extract and add up only the numbers in each cell, how could you quickly finish this in Excel?.

    Bro, but I only want to sum specific number after specific word? can I do that?
  • To post as a guest, your comment is unpublished.
    Yuri · 5 months ago
    Thank you very much!!!
  • To post as a guest, your comment is unpublished.
    jasmeet · 8 months ago
    In this formula =SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8)-1)),0)) can be put a range in place of D1 - say D1:D5 - not getting result, if i put it this way. What is the correct way to do this?
  • To post as a guest, your comment is unpublished.
    NP · 9 months ago
    Hi, I want to be able to add a number and also text to one cell (C5 all the way to C16) then get only the numbers in there to add up. But the =SumNumbers(A1) formula doesn't seem to work.
  • To post as a guest, your comment is unpublished.
    Emmy du Plooy · 1 years ago
    Hi, I hope someone can help. I like to add inside the cell +100j+50f+600m but must display/add on cell as 750 only. So you see 750 in cell but when press F2, it will show =100j+50f+600m
  • To post as a guest, your comment is unpublished.
    David · 1 years ago
    Hi. Please can someone help me. I am trying to SUM a column in EXCEL that has numbers and letters. eg 8.1km, 10.5km etc. There are 1031 lines or cells in the column. I am using this formula =SUM(LEFT(D3),LEN(D3)-2+LEFT(D4),LEN(D4)-2) which works if i want to add 2 or 3 cells together but i cant use this for 1031 cells. i wanted to add a : in place of the + but it comes back with an error and will not allow me to create the formula. Any help will be greatly appreciated
  • To post as a guest, your comment is unpublished.
    Mrityunjay · 1 years ago
    Hi,
    How to multiply 10 pcs *200,

    not 10*200
  • To post as a guest, your comment is unpublished.
    Giovanni · 1 years ago
    Hi,

    Thank you for this post! I am keen on using the VBA code and I was thinking if it would be possible to have a multivariable function so that you can sum the numbers from different column cells and write something like SumNumbers(A1:C1), for example.


    Many Thanks


    Giovanni
  • To post as a guest, your comment is unpublished.
    Anna · 2 years ago
    hi can anyone help me how to add below numbers with letters? i tried to do like but it doest work i think i miss something in the formula=SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8)-1)),0)). please help.Thanks.

    2L
    5D
    10S
    3L
    11D
    16S
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Anna,
      First, you should extract the letters into another helper column, and then apply the above formula, after entering this formula, please remember to press Shift + Ctrl + Enter keys together. see the following screenshot image:
  • To post as a guest, your comment is unpublished.
    JudyW · 2 years ago
    Hi There! I have two types of numbers in one column, one is numeric numbers, the other is a character followed by a number (such as n20, n30, etc). I wish to add up those numbers with an "n". I applied your guidance for "Sum Cells Containing Text And Numbers Based On The Certain Text With Formula", but it did not work. Is there any other method? Thanks in advance!
  • To post as a guest, your comment is unpublished.
    Ravi Sharma · 2 years ago
    April May June July Total
    1100 / 65658 1200/65657 1500/65656 2100/65650 ?

    i want to total only 1100+1200+1500+2100
    Other not a value only Receipt no. please help any
  • To post as a guest, your comment is unpublished.
    Ravi Sharma · 2 years ago
    How To Sum of selected word in cell in excel

    Exam

    1100/656480

    1100 is a amount and 656480 is recpt. no. but i want to total only 1100
  • To post as a guest, your comment is unpublished.
    Laura · 2 years ago
    Hello,
    The =SUM(IF(ISNUMBER(FIND(D1,$B$2:$B$8)),VALUE(LEFT($B$2:$B$8,FIND(D1,$B$2:$B$8)-1)),0)) - worked perfectly, except that my text is on the right.
    I tried to change the formula however that is not working... i just keep getting #VALUE!

    Can anyone help?
    • To post as a guest, your comment is unpublished.
      LeRoy · 2 years ago
      Remember to press Ctrl+Shift+Enter after changing the formula, not just Enter.
  • To post as a guest, your comment is unpublished.
    Colt · 2 years ago
    Can anyone help me with a sum formula?
    I'm trying to sum a column with cells that contain both numbers and text.
    I want to sum the numbers to the left of the text letter. sample:
    3P30
    2P30
    2P30
    2P30
    1P20
    3P30
    1P20
    3P50
    3P30
    3P60
  • To post as a guest, your comment is unpublished.
    Angela · 2 years ago
    How to include decimals in the "=SumNumbers(A1)" formula?
    Thank you!
  • To post as a guest, your comment is unpublished.
    Gil Michael Regalado · 2 years ago
    Here's a simple script that accepts single dimension and 2 dimensional selection range of cells with each cell containing string or numbers but with each cell reduced to single numbers and returns the sum of all cells.


    Option Explicit

    Function SUMCELLS(InputRange As Range) As Double
    Dim Arr() As Variant
    Arr = InputRange
    Dim R As Long
    Dim C As Long
    SUMCELLS = 0

    For R = 1 To UBound(Arr, 1) ' First array dimension is rows.
    For C = 1 To UBound(Arr, 2) ' Second array dimension is columns.
    Debug.Print Arr(R, C)
    Dim NumericValue As Double
    Dim StringValue As String

    StringValue = Trim(CStr(Arr(R, C)))
    If (Not (StringValue = "")) Then
    NumericValue = ONLYDIGITS(StringValue)
    SUMCELLS = SUMCELLS + NumericValue
    End If
    Next C
    Next R
    End Function

    Function ONLYDIGITS(s As String) As String
    Dim retval As String
    Dim i As Integer

    Dim periods As Integer
    periods = 0

    retval = ""
    '
    For i = 1 To Len(s)
    Dim char As String
    char = Mid(s, i, 1)
    If (char >= "0" And char
  • To post as a guest, your comment is unpublished.
    Kimberly Koester · 2 years ago
    I was able to use this formula perfectly, but I would also like to add a condition to only include certain numbers in the SUM formula if the value in another column = "defects".

    My original formula (using your original post) is:
    =SUM(IF(ISNUMBER(FIND("hrs",E17:E38)),VALUE(LEFT(E17:E38,FIND("hrs",E17:E38)-1)),0))

    Where E17:E38 contains the text and numbers that I want to sum
    And B17:B38 contains the values "features" and "defects"

    How can I adjust the formula to only SUM those numbers in column E if the value in column B = "defects"?

    Thanks in advance!

    Kimberly,
  • To post as a guest, your comment is unpublished.
    fazlul haque · 2 years ago
    How to convert between text and number using KUTOOls ?
  • To post as a guest, your comment is unpublished.
    MIKE · 2 years ago
    GENUIS.... Thanks ever so much
  • To post as a guest, your comment is unpublished.
    Jenny · 3 years ago
    This is super helpful! I'm trying to get the ARRAY to work with a column of cells that each contains a decimal number and text (e.g. .2 A; .5 C; .1 X etc), and I keep getting a result of 0. If I change the numbers to whole numbers, the result is correct. Help?
  • To post as a guest, your comment is unpublished.
    kc · 3 years ago
    is the any way to rewrite the vba code to sum multi cells instead of one? I have about 8 columns of data and i want the sum at the end.
  • To post as a guest, your comment is unpublished.
    Paul Burchfield · 3 years ago
    Is it possible to change the SumNumbers formula to add all the numbers in a column of mixed cells? For example, each column has 1A, 2B, 3C and I would like the formula at the bottom of the column to tell me 6.
  • To post as a guest, your comment is unpublished.
    Madelene · 3 years ago
    The SumNumbers macro saved me many hours of manual work. Thank you!
  • To post as a guest, your comment is unpublished.
    sadekul islam · 4 years ago
    ok, i understood this. good luck.
  • To post as a guest, your comment is unpublished.
    tawhid · 4 years ago
    In A1 Cell of excel I input data as like 5+6+3+1-2
    And A2 Cell of excel give the Result 13
    how it possible in excel.
  • To post as a guest, your comment is unpublished.
    sadekul islam · 4 years ago
    i think you are real teacher and good man. good luck.