Excel formula

Assuming you have data from B2 to b5...Use this formula..

=SUM(INT(B2:B5))

Enter by Ctrl+shift+enter

Workbook for your reference...

Saurabh....
sum.xlsx

Hello,

Do this on your cells:

1. Select the column of amounts and press Ctrl+H

2. In the Find What box, type .

3. Click on Replace All

James

Saurabh, the sum of INTEGER amounts is not the 110214 expected, it is only 9632.

Are the names in the same cell? If so the non numerical characters (letters etc) will need stripping out.

Will the first occurence of a space be between the name and the number?

A sample of data with all variations would be good.

Thanks
Rob H

Rob H,

This is not the complete data..if you noticed no way the sum of the example that he told will be-->110214

It has more data..I have just given him reference point about how to apply the formula which will do what he is looking for..

Saurabh...

Take out the decimals from:

Banana 7.514
Mango 6.511
Grapes  8745
Apple 874.44

and you get

Banana 7514
Mango 6511
Grapes  8745
Apple 87444

The sum of those resulting numbers is 110214

Thanks
Rob H

Assuming that the first occurence of space is between the name and number, alongside each cell in the range, use this formula:

=VALUE(SUBSTITUTE(MID(A1,FIND(" ",A1,1)+1,LEN(A1)),".",""))

You can then sum the results of that column.

Thank
Rob H

It would be far more efficient to break the numbers out using a helper column, but you could do it with:

=SUM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1:A4," ",REPT(" ",255)),255)),".","")+0)

array entered with Ctrl+Shift+Enter.

thank you all, but none of the provided solution worked for me.

A) i have a huge data range, cannot remove manually.
B)  data is a mixture of alphanumeric along with the dot . which is special char to be ignored too

the result in a simple cell should be 110214

Can you upload a sample so that we can see what special character the decimal really is?

We can then use the CODE value in the SUBSTITUTE function rather than just "."

Thanks
Rob H

Top tip: post an example that actually represents the data you have. :)

Rory,

thanks your formula seems to work.  once small issue there.  if one of my cells in my range has a text only , then the formula would pop error.

for example
Banana 7.514
 Mango 6.511
 Grapes  8745
 Apple 874.44
Watermelon

then =SUM(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1:A5," ",REPT(" ",255)),255)),".","")+0)  will pop error.

Using my suggestion above in an array formula, with slight amendment for the "special character", currently assumed to be a standard decimal point/fullstop:

=SUM(VALUE(SUBSTITUTE(MID(A1:A4,FIND(" ",A1:A4,1)+1,LEN(A1:A4)),CHAR(46),"")))

Confirmed with Ctrl + Shift + Enter

Thanks
Rob H

To remove the error, when no numbers:

=SUM(VALUE(IFERROR(SUBSTITUTE(MID(A1:A5,FIND(" ",A1:A5,1)+1,LEN(A1:A5)),CHAR(46),""),0)))

Thanks
Rob H

Even this will do what you are looking for..

=SUM(IFERROR((SUBSTITUTE(B2:B6,".","")*1),0))

Your workbook for your reference...

Enter by ctrl+shift+enter...

Rob H

I didn't realise that..thanks for correcting...

Saurabh...
sum.xlsx

Saurabh,

You're assuming that the numbers are in separate cells to the text, which is not the case.

Ahh Rory..Yeah but again i'm not sure if it's same text..Again i'm going to refer to your top tip because that helps in providing solution in 1go..

Saurabh, you are still assuming names in one column and numbers in the next.

Going by Flora's comment:

B)  data is a mixture of alphanumeric along with the dot . which is special char to be ignored too

I am assuming that the data is all in one column.

Thanks
Rob H

It has to be in the same cell based on Thora's response to my formula... ;)

My interpretation of English always mess up things for me.. :-)

As i thought his values has alphanumeric characters and want to sum only numbers ones as shown in my example...

Attached is an exmple of how this can be accomplished using VBA as below:

Function totalMyStrings(ByVal sumRange As Range)
For Each c In sumRange
    totalMyStrings = totalMyStrings + removeChars(c.Value)
Next c
End Function
Function removeChars(sInput As String) As Long
    Dim i As Integer
    Dim sResult As String
    Dim sChr As String
    For i = 1 To Len(sInput)
        sChr = Mid(sInput, i, 1)
        If IsNumeric(sChr) Then
            sResult = sResult & sChr
        End If
    Next
    removeChars = sResult
End Function

Select allOpen in new window

PLease see attached for example.
U--VBA-example.xlsm

@Saurabh,

Your English is considerably better than some people I know for whom it's their first language! :)

Thank you very much everyone for your contribution.


I wish i could spread the points. the only formula that worked for me was Rory's

however, i thank everyone of you for chipping in for helping me.

@ Rory.. Thanks for the kind words..You made my day.. :-)... 500 points from me to you... ;-)