Searching a cell for 1,2,3, or 4 digits

[Dreamdweller]

I'm trying to add to a stem and leaf formula that I have. Problem is that I have to use different cells for values. The values have to be either 2, 3, or 4 numbers. I can't use something like 23 in the same range as 123 or in 1234 since the code I am writing only works for 2,3, or 4 digits. So I'm going to try and alter it but I need to know if there is a formula to search whether a cell has 2, 3, or 4 digits in it.

So basically the code would look something like this:

If cell A1 has 2 digits (NOT 2 values) THEN ...
If cell A1 has 3 digits THEN...
If cell A1 has 4 digits THEN...

I'll add the rest of what I think will work but I just need to have something to count how many digits each cell contains.

 

[dermie_72]

Try using Len, in an imbedded If statement.

if(Len(A1)=2, then, if(Len(A1)=3,then,if(len(A1)=4,then, else do this))))

 

[Rick Rothstein]

I can't use something like 23 in the same range as 123 or in 1234 since the code I am writing only works for 2,3, or 4 digits.

It is hard to imagine that the formula cannot be adapted to work with numbers of different length automatically. Why don't you post the formulas you have for each of the different lengths and let us see if we cannot combine them into a single formula for you.

 

[Dreamdweller]

It is hard to imagine that the formula cannot be adapted to work with numbers of different length automatically. Why don't you post the formulas you have for each of the different lengths and let us see if we cannot combine them into a single formula for you.

Very well....There are basically 3 formulas. The one formula for 4 digit numbers, I know how to modify it to work for numbers that have at least 3 digits in them. That code is this:

{=IF(COLUMNS($J31:J31)<=$R31,INDEX(RIGHT($G$28:$G$39,2),SMALL(IF(LEFT($G$28:$G$39,2)=$I31&"",ROW($G$28:$G$39)-ROW($G$28)+1),COLUMNS($J31:J31))),"")}

The J31 is the cell that starts the first number. R31 is the "count" of numbers that are found. G28-39 is the table array of values. I31 places the first 2 digits. The RIGHT obviously is the right 2 digits.
This is the one I know how to modify to get just 3 if I want.
The next code, however is a nightmare. It is used if there are only 2 digits:

=REPT("  0 ",COUNTIF($B$14:$B$25,D17*10+0))&REPT(" 1  ",COUNTIF($B$14:$B$25,D17*10+1))&REPT(" 2  ",COUNTIF($B$14:$B$25,D17*10+2))&REPT(" 3  ",COUNTIF($B$14:$B$25,D17*10+3))&REPT(" 4  ",COUNTIF($B$14:$B$25,D17*10+4))&REPT(" 5  ",COUNTIF($B$14:$B$25,D17*10+5))&REPT(" 6  ",COUNTIF($B$14:$B$25,D17*10+6))&REPT(" 7  ",COUNTIF($B$14:$B$25,D17*10+7))&REPT(" 8  ",COUNTIF($B$14:$B$25,D17*10+8))&REPT(" 9  ",COUNTIF($B$14:$B$25,D17*10+9))

Finally the last code works for any number with a decimal as long as it only goes to the hundredths value. The numbers to the left of the decimal can be any amount.

{=IF(COLUMNS($E52:E52)>$O52,"",INDEX(RIGHT($B$49:$B$60*100,2),SMALL(IF(INT($B$49:$B$60)=$D52,ROW($B$49:$B$60)-ROW($B$49)+1),COLUMNS($E52:E52))))}

This code is similar to the first one in the sense that E52 is the for number or "Stem." The O52 is the "Count" of values that are found. The B49-60 is the table array.

If it is easier you can just go to this link https://people.highline.edu/mgirvin/ExcelIsFun.htm and use the "Find" option and type 586. It should go to this file called WorkbookExcelMagicTricks586-590. Open it and go to sheet 589 (an). It is where I got the code for it.

 

[Dreamdweller]

I think just use that link I sent. It might be easier to see it that way.

I have tried to combine the LEN that dermie suggested. I can get the LEN function to work for something simple like making some random text appear if the cell contains 2 or 3 or 4 numbers. It seems easy in that regard. But when I try to copy and paste those long codes into the LEN, nothing appears.

Whatever you can suggest I appreciate.

Bryan

 

[Dreamdweller]

So far I've combined it like this:

=IF(LEN(B2)=4,IF(COLUMNS($E6:E6)<=$M6,INDEX(RIGHT($B$2:$B$13,2),SMALL(IF(LEFT($B$2:$B$13,2)=$D6&"",ROW($B$2:$B$13)-ROW($B$2)+1),COLUMNS($E6:E6))),""),IF(LEN(B2)=2,REPT(" 0 ",COUNTIF($B$2:$B$13,D6*10+0))&REPT(" 1 ",COUNTIF($B$2:$B$13,D6*10+1))&REPT(" 2 ",COUNTIF($B$2:$B$13,D6*10+2))&REPT(" 3 ",COUNTIF($B$2:$B$13,D6*10+3))&REPT(" 4 ",COUNTIF($B$2:$B$13,D6*10+4))&REPT(" 5 ",COUNTIF($B$2:$B$13,D6*10+5))&REPT(" 6 ",COUNTIF($B$2:$B$13,D6*10+6))&REPT(" 7 ",COUNTIF($B$2:$B$13,D6*10+7))&REPT(" 8 ",COUNTIF($B$2:$B$13,D6*10+8))&REPT(" 9 ",COUNTIF($B$2:$B$13,D6*10+9)),""))

It seems to work initially but then after I alter the numbers it seems to stop working. Not sure if my coding is wrong.

 

[Dreamdweller]

Anyone have any thoughts?