LEN Function, Get rid of 0.333333 and replace by 0 (zeroe)

[david gonzalez]

Hi Friends,

I'm using a Len Function to count some digits with in string.

For Example, in G12 Down I have :

_____G:12_____________
12 l 10-16-17-22-23 l
13 l 03-11-14-24-26 l
14 l 17-19-25-28-35 l


So in next Cell i'm using LEN Function to do the count,

Please Login or Register  to view this content.

Note : i changed/combined the digits after the plus sign, Also i add TRIM but it didn't work
it get changed for other s fraction..


When LEN find
1 Digit, it write 1 <- Perfect
2 Digit, it write 2 <- Perfect
3 Digit, it write 3 <- Perfect
BUT
When LEN find 0 Digit, it write 0.333333 <- IMPERFECT Lol

i would like to replace the 0.333333 to 0 (Just the Digit zeroe)

How can i do that ?, any help please?


Best regards

David

[Tony Valko]

What exactly are you wanting to do?

[AlKey]

Maybe this

=INT(LEN(G12)+1/3)

[FDibbins]

Why are you adding +1/3? that is what is causing the 0.3333333

[david gonzalez]

Hi mr tony,

Yes, i made a mistake on my first post.

It have to do with G12, and with Other Cell AM9 (was hidden)

Im Using GetMatch Function to see if AM9 Match G12 down.

In Cell AM9 is the following combination -> 03-13-20-21-34

and (red numbers are the match )

Now, if we observe in G12 and G14 have no match any digit from AM9,But G13 it have a 1 match
_____G:12_____________R12____
12 l 10-16-17-22-23 l 0.333333 l
13 l 03-11-14-24-26 l 1 l
14 l 17-19-25-28-35 l 0.333333 l

So, the LEN Function count and show If how many numbers got matched

Everything is find, except that when LEN find out a No match or 0 Match
in Cell R12, It show 0.333333, and i would like to get Just the 0 (the digit zero)

btw the LEN is located in Cell AH13

Don't know if was explained correct..sorry


Thank you!!!

Best regards

David

[david gonzalez]

Hi Mr FDibbin,

If i don't add the +1/3, It count more, 2 ( and should be 1)

With out it the plus tinghi, the 03 It count as 2 digit, not as 1 number ..


Thanks

David

[david gonzalez]

Mr Mr Alley,
If I

Please Login or Register  to view this content.

then it get count the 2 digits instead of 1 number


Thanks


David

[Tony Valko]

Is this for a lottery program?

If so, it would probably be easier to put each number in its own individual cell.

[david gonzalez]

Hi Mr Tony,

Yes, is more easier, but most of the VBA code are maded to work in a single cell,
that's why it get a lil difficult to deal with..



Best regards

David

[Tony Valko]

Well, I'm not much of a programmer so I can't be much help with VBA problems.

Good luck!

[FDibbins]

Hi Mr FDibbin,

If i don't add the +1/3, It count more, 2 ( and should be 1)

With out it the plus tinghi, the 03 It count as 2 digit, not as 1 number ..

What am I missing here?

You have a formula that is counting the lenth of a string in a cell. That length is 21 characters, including the pipes and the spaces. Unless you change the contents of the cell, that will not change.

By adding 1/3 on the end, you are not chagingthe contents in anyway, all you are doing is changing it from 21 to 21.33333

12 l 10-16-17-22-23 l

Based on your sample above, what is your expected answer?

[david gonzalez]

Hi Mr Tony,

Today started to learn the VBA for dummy, hope my old head catch all of the basic on programming..

Thank you Sir!!

Best regards

David

[AlKey]

We'll, I guess we learnt nothing

[david gonzalez]

Hi Mr FDibbin,

Okay, lets me explain you skipping the content of cell G12.

I'm using a Get Match Formula to get some match between Cell G12 and AM9
(numbers with leading zero, and using dash "-")

then the matched numbers are showed in Cell R12 down
Example :

Numbers
matched___l_LEN Here_
__R12____l___AH13__
___21_____l____1____ <-- Correct count 1
___0______l_0.333333 <-- When no match or 0 match LEN Count as 0.333333
___0______l_0.333333 <-- When no match or 0 match LEN Count as 0.333333
___13_____l___1_____ <-- Correct count 1
___20_____l___1_____ <-- Correct count 1
___03_____l___1_____ <-- Correct count 1
___0______l_0.333333 <-- When no match or 0 match LEN Count as 0.333333
___13-20__l___2_____ <-- Correct count 2
___03-13__l___2_____ <-- Correct count 2
___0______l_0.333333 <-- When no match or 0 match LEN Count as 0.333333



Thanks

David

[martindwilson]

back to what i said umpteen posts ago put everything in separate cells
from the start you can use code to do that
you can always split your original date with text to columns using - as a delimiter it would save loads of effort

[david gonzalez]

Hi Mr Alkey,

I started today with VBA, i don't know how long it takes me to deliver a basic code, LOL


Thanks

David

[AlKey]

=If(r12=0,0,your formula

[david gonzalez]

Hi Mr Martindwilson,

Yes is true, i have to organize everything on my sheet, in order to start using separated cells
most of the time i get stuck, due to "-" and single cell tinghi.

Thank you sir,


Best regards

David

[david gonzalez]

Hi Mr Alkey,

=If(r12=0,0,your formula

You mean the LEN formula with the plus?

I tried and when found zero, is identified like 0, but when find 1 or 2,It still identify like zero..

I get lost... lol


Thanks


David

[Tony Valko]

I have a lottery file for a pick 5 or pick 6 game.

I designed it for people that play a lot of tickets per draw.

You can enter the numbers from up to 250 tickets (or groups of numbers).

If you want to play 25 groups of numbers, unless you specifically ask for 25 individual tickets, they will give you 5 tickets with 5 groups of numbers on each ticket.

It's an Excel 2002 file in the *.xls format. Want to see it?

[david gonzalez]

Hi Mr Tony,

Yes, i would like to see it.... what i'm doing is something similar, but all of this adding (root, doubles triples, etc,ect)
are to filter out any tickets that don't match the Bell curve criteria..



Thanks


David

[david gonzalez]

Hi Mr Tony,

I can send you mine xls but where? is big, so i don't think i can post it here..
(i believe the limit here is 2 or 3 Mb )


Thanks


David

[Tony Valko]

This file is just for checking to see if you have any winners.

Ticket Checker.xls

You'll have to enable macros. It has two macros, one for each sheet that simply clears the data that was entered. Click the Reset button.

Enter the numbers that are drawn into the green cells at the top.

Enter the numbers from your tickets. Type in a number then use the right arrow key to move to the next cell. After the last number is entered on a row and you use the right arrow key the active cell will automatically go to the next row.

The sheets are protected but no password is needed to unprotect.

[Tony Valko]

Every now and then I'll get a lot of tickets.

What I do is I write a number on the tickets 1, 2, 3, 4, 5, etc.

Then, it's easy to find the ticket(s) that are winners. You'll see how the number rows are labeled.

[david gonzalez]

Hi my friends!!

Well, i found a Function that when find 0 or empty cell, it show the 0 (digit zero) as wanted,

But now is the opposite :
-.when find one (1) number (e.i 12, 01, or 35,) the function count as 2 digit ( i need to count as 1 number )
-.when find two (2) numbers (e.i 12-13, 11-35, etc) the function count as 4 digit ( i need to count as 2 number )
note: find with out the word "numbers"

This is the function inserted :

Please Login or Register  to view this content.

Please any suggestion ??


Best Regards

David

[martindwilson]

'=if(a1&"-"="0-",0,len(a1&"-")-len(substitute(a1&"-","-","")))

[david gonzalez]

Hi mr Martindwilson,

when cell have no value, the Formula it recognize it at 1 (should be 0)
When cell have 1, 2 or 3, it recognize it correct
But in each cell it show the lil green dot (top left corner)

Numbers ______Count
Col__R12____l___S12___
____________l____1____ ---> no Ok,it should be 0 (because empty cell,no numbers found)
____________l____1____ ---> no Ok,it should be 0 (because empty cell,no numbers found)
____________l____1____ ---> no Ok,it should be 0 (because empty cell,no numbers found)
____35______l____1____ -> Ok
____36______l____1____ -> Ok
____19______l____1____ -> Ok
____23______l____1____ -> Ok
____35______l____1____ -> Ok
____28-35___l____2____ -> Ok
__04-02-22__l____3____ -> Ok

Best Regards

David

[martindwilson]

well if its blank and not 0
=IF(A1&"-"="-",0,LEN(A1&"-")-LEN(SUBSTITUTE(A1&"-","-","")))

[david gonzalez]

Hi mr Martindwilson,

Yes !!!!!!!!!!!!!!!!!!!!!!! It worked !!!!!

Thank you sir, do you know that i have had 3 days searching the net for such problem
and No one had a same problem before ( yes, because i could not find a solution, lol)

Thank you Mr martin, Appreciated a Lot !!!!!

Best regards

David