Formula or Function for count value separated by commas (,)

I have noooo idea how you get 4.5 or 3.8, but this will give you the answer for the 1st sample...
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

18,22,15,11,7 then show 5 --- There are five numbers, so result is 5.

18(500),22,15,11,7 then show 4.5 --- There are four numbers and one with a parenthetical, so result is 4 plus the decimal of 500/1000 ?

18(200),22(600),15,11,7 then show 3.8 --- There are three numbers, and two with parentheticals, so result is 3 plus the decimal of (200+600)/1000 ?

What happens if the parenthetical values add up to more than 1000? e.g. "18(500),22(700),15,11,7". Is that 4.2? I assume it wouldn't be 3.12 since that could also be achieved with "18(100),22(20),15,11,7".

The following Function (insert into Standard Module) should do the trick. If the string of values is in A1, then in B1 use the formula

=cdelim(A1)

Can be filled down/across, copied elsewhere, etc. Whichever cell you want to parse, set that as the cell in the formula. It's currently coded to only work on one cell.

Please Login or Register  to view this content.

Yes it is.... Thanks Paul...ur provided UDF working as per my need...

thanks a lot

Actually when we run this UDF in main database then we got in few cell 1(300) or 2(500) data in following shape
1(300) = 1(200-500), 1(600-900)
2(500) = 2(0-500), 2(500-1000)

actaully in few record between () full value exit insted of cacluated

in this we analize whole data in between() small value first then higher value seccond

for eg (600-900), (200-500) etc..
Santosh

I think this slight change should accommodate any parentheticals that contain math, e.g. 100+200, 500-100. I also found a mistake at the end of the tmpSum line, in which I was keeping too many characters. Luckily the Val function ditched the non-numeric characters at the end and still calculated properly. With the math functions, though, that wasn't working and I had to fix it. I ended up changing the Val function to the Evaluate function so it would actually perform the math shown in the parentheses, then include it in tmpSum. Hopefully that makes sense!

Please Login or Register  to view this content.

That is a math problem, not an Excel problem. The function is working exactly as intended.

(200-500)/1000 is not 0.3, it is -0.3. So 2 + (-0.3) = 1.7

What mathematic function do you think you could use to change negative values to positive ones, and how might you adjust the code to do so?

What I'm saying is this:

There are several ways to convert negative values to positive values.
1. Multiply negative results by -1
2. Get the absolute value of the result (always positive)
3. Subtract the result from the result of subtracting the result from itself. (-300-(-300)-(-300)=300)
4. Some other silly method.

Obviously the first two methods are simple, and easy to code into my solution.

Having said that, here's how the Abs function (absolute value) would be coded..

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.