[Solved] Finding SUM on a single single cell ignoring TEXT

[dangerduck]

Hi Exceldemy members,

I come with a silly question hoping theres a rather simple solution for this...

Is there a formula that SUMS UP the total of all the numbers in a single cell ignoring all the text as per the screenshot below

Much appreciated if it can be found if not obviously ill just have to get the calculator out lol

 

[Lutfor Rahman Shimanto]

Hello DangerDuck,

Thank you for reaching out to us at ExcelDemy with your question. You were looking for an idea to sum up, the total of all numbers in a single cell while ignoring any text. Using VBA, we develop a user-defined function named IgnoreTextSumUpNumber to solve the issues. To use the procedure, you must copy the code in the Module box and save it with the Macro-Enabled extension. Secondly, you must insert spaces at the beginning and end of a number while typing text string in a cell.
Here is the code:

Function IgnoreTextSumUpNumber(singleRange As Range, Optional separatorString As String = " ") As Double

    Dim Numberlist As Variant, NumerItem As Long
    
    For Each component In singleRange
        Numberlist = Split(component, separatorString)
        
        For NumerItem = LBound(Numberlist) To UBound(Numberlist) Step 1
            IgnoreTextSumUpNumber = IgnoreTextSumUpNumber + Val(Numberlist(NumerItem))
        Next NumerItem
    Next component

End Function

If you need further assistance or have other questions, please don't hesitate to contact us. Our team is always here to support you and help you improve your Excel skills.

Best regards,
Lutfor Rahman Shimanto (ExcelDemy Team)

 

[dangerduck]

Hi

Hello DangerDuck,

Thank you for reaching out to us at ExcelDemy with your question. You were looking for an idea to sum up, the total of all numbers in a single cell while ignoring any text. Using VBA, we develop a user-defined function named IgnoreTextSumUpNumber to solve the issues. To use the procedure, you must copy the code in the Module box and save it with the Macro-Enabled extension. Secondly, you must insert spaces at the beginning and end of a number while typing text string in a cell.
Here is the code:

Function IgnoreTextSumUpNumber(singleRange As Range, Optional separatorString As String = " ") As Double
Dim Numberlist As Variant, NumerItem As Long
For Each component In singleRange
Numberlist = Split(component, separatorString)
For NumerItem = LBound(Numberlist) To UBound(Numberlist) Step 1
IgnoreTextSumUpNumber = IgnoreTextSumUpNumber + Val(Numberlist(NumerItem))
Next NumerItem
Next component
End Function

If you need further assistance or have other questions, please don't hesitate to contact us. Our team is always here to support you and help you improve your Excel skills.

Best regards,
Lutfor Rahman Shimanto (ExcelDemy Team)

Hi lutfor,

Much appreciated in sharing the VBA codes with me - It worked perfectly - This has made my life easier...Ill be sharing this with my collegues & friends thats in need of Excel help.

Thanks

Mathews

 

[Lutfor Rahman Shimanto]

Hi

Hi lutfor,

Much appreciated in sharing the VBA codes with me - It worked perfectly - This has made my life easier...Ill be sharing this with my collegues & friends thats in need of Excel help.

Thanks

Mathews

Hi Mathews,

You're welcome! I'm delighted to hear that the VBA code worked for you. We are always here to assist, and it makes us happy to know that we could make your life easier. If you have more questions or need more help, feel free to get in touch.

Regards,
Lutfor Rahman Shimanto (ExcelDemy Team)