Hi,

I wanted an excel formula which is described here:http://www.computing.net/answers/of...

Now I want that if the cell contains numbers as well as characters it will take only numbers and add those.

Example: A cell contains 2900(perfume). It will take only 2900 and add that with the others.

Thanks in advance,

Souvik

There is no built-in Excel function that will do what you are asking. It would probably require either multiple steps in Excel or VBA to accomplish your goal.

In order for us to help you, you would need to be very specific about what your data looks like. For example, is it always a parenthesis that separates the numbers from the text, etc.

Excel is going to treat strings such as 2900(perfume) as text and we would need to find a way to extract the number.

For example, with 2900(perfume) in A1, this formula would extract a number that could be summed along with other numbers:

=MID(A1,1, FIND("(",A1) - 1)*1

Something like that would need to be done for every cell where you have text mixed with numbers.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Every time the cell will contain number and then some text in parenthesis...........

and also i need this for all the rows as it was for the circumstances in the given link....

Well, if the cells will always "contain number and then some text in parenthesis", haven't I already provided the solution?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Yes you have.........but I can not configure the whole thing........please give me the total code by which I can add all the cells of a row containing number as well as text in paranthesis............ It will be very helpful to me if u can right for............

M=A+B+C+D+E+F+G.............where A,B,C..........will contain either number or number and text in paranthesis.....

As I said in my first response, you cannot do this in one step. You first need to convert all of the numbers/text to numbers and then add the resulting numbers. For example, if your data is in A1:L1. you need to put the "extraction formulas" someplace else, for example in A2:L2 or Row 1 of another sheet or something like that.

In other words, you have to extract all the numbers on a cell by cell basis and then sum the results. You can't perform the extraction and summing with one single formula.

So, if your data is in A1:L1, put this in A2 and drag it over to L2:

=IF(ISERROR(FIND("(",A1)),A1,MID(A1,1,FIND("(",A1)-1)*1)

What this should do is check for a

(in each cell. If it doesn't find one, then ISERROR will return TRUE and just the number will be returned. If it does find a(, then it will strip off the(and everything after it, leaving just the number.Once you have a row full of extracted numbers, you can then sum them.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History