Excel formula

Hello,

i have cells like this

Banana 7.514
Mango 6.511
Grapes  8745
Apple 874.44

i need a formula to result sum of only numbers disregarding any dot or alphas.

the desired result should be 110214
LVL 6
FloraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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
0
James HodgeManaging DirectorCommented:
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
0
Rob HensonFinance AnalystCommented:
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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Saurabh Singh TeotiaCommented:
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...
0
Rob HensonFinance AnalystCommented:
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
0
Rob HensonFinance AnalystCommented:
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
0
Rory ArchibaldCommented:
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.
0
FloraAuthor Commented:
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
0
Rob HensonFinance AnalystCommented:
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
0
Rory ArchibaldCommented:
Top tip: post an example that actually represents the data you have. :)
0
FloraAuthor Commented:
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.
0
Rob HensonFinance AnalystCommented:
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
0
Rob HensonFinance AnalystCommented:
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
0
Rory ArchibaldCommented:
You can handle that with:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saurabh Singh TeotiaCommented:
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
0
Rory ArchibaldCommented:
Saurabh,

You're assuming that the numbers are in separate cells to the text, which is not the case.
0
Saurabh Singh TeotiaCommented:
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..
0
Rob HensonFinance AnalystCommented:
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
0
Rory ArchibaldCommented:
It has to be in the same cell based on Thora's response to my formula... ;)
0
Saurabh Singh TeotiaCommented:
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...
0
SteveCommented:
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

Open in new window


PLease see attached for example.
U--VBA-example.xlsm
0
Rory ArchibaldCommented:
@Saurabh,

Your English is considerably better than some people I know for whom it's their first language! :)
0
FloraAuthor Commented:
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.
0
Saurabh Singh TeotiaCommented:
@ Rory.. Thanks for the kind words..You made my day.. :-)... 500 points from me to you... ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.