How to count values seperated by semicolons?

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,841
Hi everyone,

Here is my example table:
<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=198 x:str><COLGROUP><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 width=80>*</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 width=118>A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>1;2;5;6</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl22>30;31;33;35;36;37</TD></TR></TBODY></TABLE>

I want to count how many numbers are there in cell A1:A2 which are seperated by semicolons.

Here is the result table:
<TABLE style="WIDTH: 209pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=278 x:str><COLGROUP><COL style="WIDTH: 60pt" width=80><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 60pt" width=80><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 width=80>*</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=118>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 width=80>B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>1;2;5;6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:num>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24>30;31;33;35;36;37</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 x:num>6</TD></TR></TBODY></TABLE>


Please, can you help me with this question.

Thanks and Regards,
Moti
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
You could try:

=LEN(a1)-LEN(SUBSTITUTE(a1,";",""))+1

This assumes that a cell does not start or begin with just a semicolon

Hope that helps.
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,841
Hi Schielrn,

Thank you very much for giving me an immediate reply.
It is a perfect formula for my question. It is working fabulously.

Thanks and Regards,
Moti
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,841
Hi Schielrn,

TABLE A-Suppose if I have an example table as below: <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>

<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" border=0 cellSpacing=0 cellPadding=0 width=199><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 60pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=80 noWrap>
*<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 89pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=119 noWrap>
A<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1;2;5;6<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
2<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
30;31;33;35;36;37<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
0;4<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
4<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
0<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
TABLE B-Using the formula given lastly by you I get this result:<o:p></o:p>
<TABLE style="WIDTH: 209pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" border=0 cellSpacing=0 cellPadding=0 width=279><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 60pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=80 noWrap>
*<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 89pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=119 noWrap>
A<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 60pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=80 noWrap>
B<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1;2;5;6<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
4<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
2<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
30;31;33;35;36;37<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
6<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
0;4<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
2<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
4<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
0<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
<o:p></o:p>
TABLE C-But can I get a result as shown in table below:<o:p></o:p>
<TABLE style="WIDTH: 209pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" border=0 cellSpacing=0 cellPadding=0 width=279><TBODY><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 60pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=80 noWrap>
*<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 89pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=119 noWrap>
A<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; WIDTH: 60pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom width=80 noWrap>
B<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1;2;5;6<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
4<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
2<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
30;31;33;35;36;37<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
6<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
3<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
0;4<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
1<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt"><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
4<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
0<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0.65pt; PADDING-RIGHT: 0.65pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 0.5pt solid; PADDING-TOP: 0.65pt" vAlign=bottom noWrap>
0<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
<o:p></o:p>
Please can you modify the formula so that it ignores the Zero Values and obtain the result as shown in table C<o:p></o:p>
<o:p></o:p>
Thanks and Regards,
Moti
<o:p></o:p>

<o:p></o:p>
 

facethegod

Well-known Member
Joined
Aug 1, 2006
Messages
767
Perhaps....

=LEN(SUBSTITUTE(";"&A1,";0",""))-LEN(SUBSTITUTE(SUBSTITUTE(";"&A1,";0",""),";",""))
 

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
1,841
Hi facethegod,

Your answer was fabulous, the formula is working perfectly. Thanks for your help.

Thanks and regards,
Moti.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,444
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top