Count # of Digits in a CEll

[hrayani]

Hello To All,

Is there any formula which can count the number of digits in a cell - whether the cell is a manually entered value or a formula result.

For Example

Cell Value = 568793
# of Digits Are (6)

Awaiting reply.

Best Regards,

Humayun Rayani

 

[repairman615]

The post from Sachin was a key on the maching digits.

I do not easily see how to modify this to "Unique".

Be sure to see the updated post #19...corrects the 5's.

I will check back tomorrow if not sooner.

 

[hrayani]

Sure Repairman615... pls take your time..

and yes i have updated the formula.. thanks again..

 

[hrayani]

Try given formula in one cell & need to put the value in cell C6 !

=IF(ISERROR(FIND("1",C6,1))=TRUE,1,"")&IF(ISERROR(FIND("2",C6,1))=TRUE,",2","")&IF(ISERROR(FIND("3",C6,1))=TRUE,",3","")&IF(ISERROR(FIND("4",C6,1))=TRUE,",4","")&IF(ISERROR(FIND("5",C6,1))=TRUE,",5","")&IF(ISERROR(FIND("6",C6,1))=TRUE,",6","")&IF(ISERROR(FIND("7",C6,1))=TRUE,",7","")&IF(ISERROR(FIND("8",C6,1))=TRUE,",8","")&IF(ISERROR(FIND("9",C6,1))=TRUE,",9","")&IF(ISERROR(FIND("0",C6,1))=TRUE,",0","") 
<!-- / message -->

Dear Sachin. medhi

this is your post # 9 about which i told you that i failed to understand..
sorry dear.. its too working just perfect... as repairman's formula

the only difference is your formula is giving answers with ,,, like 0,1,2,3
whereas, repairman's formula is giving answers without ,,,, like 0123

Thanks once again for your time..

meanwhile pls look into the UNIQUE DIGITS matter too... IF YOU HAVE TIME other maybe tomorrow ... no problem

Thanks once again

Regards,

Humayun Rayani

 

[Aladin Akyurek]

Oh Yes,, it did worked perfect..

What i was trying to say is this formula is so easy that i should have knew it before...

Actually, english is not my mother or national language that is why i wasn't able to explain it properly... pls bear me..

Dear there is one more thing which i would like to ask you..

For Example cell A1 value = 564893
is there any formula which could show the missing digits like 0127 are missing in the above cell...

am I sounding illogical ???

Add the following code as a module to your workbook:

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant

If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If

aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Now invoke in B2...

Control+shift+enter, not just enter:

=ACONCAT(IF(ISNA(MATCH({0,1,2,3,4,5,6,7,8,9},MID($A$2,ROW(INDIRECT("1:"&LEN($A$2))),1)+0,0)),{0,1,2,3,4,5,6,7,8,9},""))

 

[Sachin.Medhe]

Hi Hyrani,

You can write me as Sachin !

Try this one

=IF(ISERROR(FIND(LEFT(A1,1),A1,1))=ISERROR(FIND(LEFT(A1,1),B1,1)),LEFT(A1,1),"")

Note :- this is for single len, you should input formula for next len number

 

[hrayani]

Dear Sachin,

Thanks for your help... pls provide formula for 2 lens further i will add it.. as of now i am not too sure how to add more...

Regards,

Humayun

 

[hrayani]

Dear Aladin

Thanks for your help too...

Yours one is also working Perfect.

Pls see if you can just help me regarding UNIQUE NUMBERS problem

Regards,

Humayun

 

[Sachin.Medhe]

Hi Hrayani,

Sorry, in previous post i forgot to put RIGHT function

here is your correct formula for 1 & 2 len

=IF(ISERROR(FIND(RIGHT(LEFT(A1,1),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,1),1),B1,1)),RIGHT(LEFT(A1,1),1),"")&IF(ISERROR(FIND(RIGHT(LEFT(A1,2),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,2),1),B1,1)),RIGHT(LEFT(A1,2),1),"")

I know its too huge, but i dont have other solution

Note :- Please add formula for next len

 

[hrayani]

Dear Sachin,

i tried to enter more lens in it but am only able to add 9 lens as the 10th len i.e. for 0 is not accepting coz the formula is too long.

secondaly, the result is also showing some error in it.

the formula i entered is:


=IF(ISERROR(FIND(RIGHT(LEFT(A1,1),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,1),1),B1,1)),RIGHT(LEFT(A1,1),1),"")&IF(ISERROR(FIND(RIGHT(LEFT(A1,2),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,2),1),B1,1)),RIGHT(LEFT(A1,2),1),"")&IF(ISERROR(FIND(RIGHT(LEFT(A1,3),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,3),1),B1,1)),RIGHT(LEFT(A1,3),1),"")&IF(ISERROR(FIND(RIGHT(LEFT(A1,4),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,4),1),B1,1)),RIGHT(LEFT(A1,4),1),"")&IF(ISERROR(FIND(RIGHT(LEFT(A1,5),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,5),1),B1,1)),RIGHT(LEFT(A1,5),1),"")&IF(ISERROR(FIND(RIGHT(LEFT(A1,6),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,6),1),B1,1)),RIGHT(LEFT(A1,6),1),"")&IF(ISERROR(FIND(RIGHT(LEFT(A1,7),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,7),1),B1,1)),RIGHT(LEFT(A1,7),1),"")&IF(ISERROR(FIND(RIGHT(LEFT(A1,8),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,8),1),B1,1)),RIGHT(LEFT(A1,8),1),"")&IF(ISERROR(FIND(RIGHT(LEFT(A1,9),1),A1,1))=ISERROR(FIND(RIGHT(LEFT(A1,9),1),B1,1)),RIGHT(LEFT(A1,9),1),"")


Cell A1 = 1234
Cell B1 = 2345

The answer which i am getting is 2344444
<TABLE style="WIDTH: 470pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=627 x:str><COLGROUP><COL style="WIDTH: 470pt; mso-width-source: userset; mso-width-alt: 22930" width=627><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 470pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=627 x:num></TD></TR></TBODY></TABLE>

 

[hrayani]

i guess we are still working for the matching digits formula whereas, that problem is solved from the previous formula i.e.

=IF(ISERR(FIND(0,J7,1)),0,"")&IF(ISERR(FIND(1,J7,1)),1,"")&IF(ISERR(FIND(2,J7,1)),2,"")&IF(ISERR(FIND(3,J7,1)),3,"")&IF(ISERR(FIND(4,J7,1)),4,"")&IF(ISERR(FIND(5,J7,1)),5,"")&IF(ISERR(FIND(6,J7,1)),6,"")&IF(ISERR(FIND(7,J7,1)),7,"")&IF(ISERR(FIND(8,J7,1)),8,"")&IF(ISERR(FIND(9,J7,1)),9,"")

what currently i am looking for is a unique digit formula