# 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
###### 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.

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

=SUM(INT(B2:B5))

Enter by Ctrl+shift+enter

Saurabh....
sum.xlsx
0
Managing DirectorCommented:
Hello,

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

2. In the Find What box, type .

3. Click on Replace All

James
0
Finance 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
Commented:
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
Finance 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
Finance 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
Commented:
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
Author 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
Finance 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
Commented:
Top tip: post an example that actually represents the data you have. :)
0
Author 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
Finance 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
Finance 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
Commented:
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

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

Commented:
Even this will do what you are looking for..

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

Enter by ctrl+shift+enter...

Rob H

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

Saurabh...
sum.xlsx
0
Commented:
Saurabh,

You're assuming that the numbers are in separate cells to the text, which is not the case.
0
Commented:
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
Finance 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
Commented:
It has to be in the same cell based on Thora's response to my formula... ;)
0
Commented:
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
Commented:
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
``````

U--VBA-example.xlsm
0
Commented:
@Saurabh,

Your English is considerably better than some people I know for whom it's their first language! :)
0
Author 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
Commented:
@ 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.