Formula help.

[serge]

Hi everyone,

I have in A1 : 14

and in B1 : =MID(TEXT($A1,"00"),1,1)+0 which give me : 1

and in C1 : =MID(TEXT($A1,"00"),2,1)+0 which give me : 4

My question is : What need to be change in this formula, for when I have a single digit, that the first formula don't return a : 0 ?

Example :

If I have in A1 : 2

I need in cell B1 to return an empty cell instead of 0, and in C1 the digit 2.

or should I use a different formula ?

Thank you... Serge.

 

[Aladin Akyurek]

Try...

B1:

=IF(LEN($A1)=2,MID(TEXT($A1,"00"),1,1)+0,"")

 

[serge]

I put 14 in A1

B1 return 1 and C1 return 4 which is good.

But when I enter 2 in A1 both cells B1 and C1 are empty !!!

 

[Aladin Akyurek]

I put 14 in A1

B1 return 1 and C1 return 4 which is good.

But when I enter 2 in A1 both cells B1 and C1 are empty !!!

C1 should house the formula you already have, tha is:

=MID(TEXT($A1,"00"),2,1)+0

 

[serge]

C1 is not a problem, it's B1 that I have a problem with because if I have a single digit like here number 2 as example I don't want B1 to return 0 but just an empty cell.

I have over 6000 row to work with and I just want to drag down the formula and get the right answer because after that I have a formula that will count how many digit 0, how many digit 1, how many digit 2, etc...

Thank you Aladin.

 

[Aladin Akyurek]

C1 is not a problem, it's B1 that I have a problem with because if I have a single digit like here number 2 as example I don't want B1 to return 0 but just an empty cell.

I have over 6000 row to work with and I just want to drag down the formula and get the right answer because after that I have a formula that will count how many digit 0, how many digit 1, how many digit 2, etc...

Thank you Aladin.

Are yo saying that:

B1:

=IF(LEN($A1)=2,MID(TEXT($A1,"00"),1,1)+0,"")

does not do what you want? This is what I get:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=64 align=right>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64 align=right>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>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=xl65> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 align=right>45</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=xl65 align=right>4</TD></TR></TBODY></TABLE>

Is this wrong output?

 

[serge]

Sorry Aladin, you are right I was using your formula for both B1 and C1 and of course both cell were returning blank cell.

Thank you very much now it works fine.

Best Regards... Serge.