Counting Numbers

[Steve]

Good morning Claus and thanks again for the suggested formula and UDF.

The formula

=IFERROR(CHOOSE(LEFT(JY5540,IF(LEN(JY5540)-LEN(SUBSTITUTE(JY5540,"-",))=0,1,FIND("-",JY5540)-1)),5,3,2,1,1),0)+IFERROR(IF(LEN(JY5540)>1,CHOOSE(MID(JY5540,FIND("-",JY5540)+1,IF(LEN(JY5540)-LEN(SUBSTITUTE(JY5540,"-",))=1,1,FIND("#",SUBSTITUTE(JY5540,"-","#",2))-FIND("-",JY5540)-1)),5,3,2,1,1),0),0)

Seems to work perfectly until it comes across numbers greater than 5 .
For example, if the number was as follows:

12-12 - it returns a value of 10 having taken the first number from
each string as a 1 and counting that as a five twice. In actual fact
12-12 should return a value of zero.

Would it be possible to advise how to amend the formula to ignore any
value above 5 and any other texts values that may appear in the
string, for example PU, F, UR etc all may appear at some point in the
future.

I have updated the spreadsheet with examples of this and hope you can
help.

https://onedrive.live.com/redir?resid=CB2CB4829572A60A!316&authkey=!ACtAogKt7FPYPjY&ithint=file,xlsm

Thanks in advance


Steve

 

[Claus Busch]

Hi Steve,

Am Mon, 20 Oct 2014 10:31:45 +0100 schrieb Steve:

Seems to work perfectly until it comes across numbers greater than 5 .

have another look in OneDrive


Regards
Claus B.

 

[Steve]

Hi Claus, thanks for the quick reply.

That seems to work perfectly with the only exception being where there
is only one value in the cell for example

14

12
29

etc

In that case the UDF shows a #VALUE error and the formula calculates
using the first numebr so in the example above that would be
calculated as

14 = 5

12 = 5

29 = 3

I have updated the spreadsheet with examples

https://onedrive.live.com/redir?resid=CB2CB4829572A60A!318&authkey=!AC07Sa7i3nIJuTs&ithint=file,xlsm

Thanks for your patience Claus !

Kind regards


Steve

 

[Claus Busch]

Hi Steve,

Am Mon, 20 Oct 2014 11:43:42 +0100 schrieb Steve:

That seems to work perfectly with the only exception being where there
is only one value in the cell for example

I fixed that issue
Have another look.


Regards
Claus B.

 

[Steve]

Hi Claus, again the UDF works perfectly but for one minor instance and
that is where there is a single number in the cell between 6 and 9
which throws up a #value error.

I have provided two examples on the spreadsheet in the tab called
Claus

https://onedrive.live.com/redir?resid=CB2CB4829572A60A!321&authkey=!ALlQxFFiBA9IiV8&ithint=file,xlsm

Other than that it is exactly what I was looking for so thank you very
much

Kind regards



Steve

 

[Claus Busch]

Hi Steve,

Am Mon, 20 Oct 2014 12:21:58 +0100 schrieb Steve:

Hi Claus, again the UDF works perfectly but for one minor instance and
that is where there is a single number in the cell between 6 and 9
which throws up a #value error.

have another look ;-)


Regards
Claus B.

 

[Steve]

Perfect !!

Thanks so much Claus, have a great day

Regards

Steve