Re: clear user form after entry

More amusement than confusion <g>

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in
message news:OxbLndxqIHA.4788@xxxxxxxxxxxxxxxxxxxxxxx
Hmm! I have no idea why this message (and the previous incomplete one) are
here in this thread. Obviously, I post them here by mistake and now I'll
have to go search for where they were supposed to have been posted to.
Sorry about any confusion these two posts may have caused... I am not sure
what I did to misdirect them to this thread.

Rick


"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in
message news:Oz8i7IoqIHA.4672@xxxxxxxxxxxxxxxxxxxxxxx
*** Sorry about the incomplete previous posting ***
*** I accidentally sent it before I had finished. ***

See inline comments...

I can use this constantly - if I understand it

Before you go and use this everywhere, remember that it was designed for
your specific case... in effect, find the first digit that is not a zero
(notice that the array and listing of digits do not have a zero in them),
which is also why I said this formula only applies IF the first character
after the leading zeroes is a digit (it will fail to work correctly if a
non-digit ever follows the leading zeroes).

and Gosh I hate to ask but could you explain how it works -

I'll try.

I think the Find({1,2,3,4,5,6,7,8,9} is an array function - I believe
it uses the &"123456789" constant
but I am really confused as to the MIN function and the 99 value and
just basically how the formula flows.

I guess the best place to start is by giving an overview of what the
formula
does. It finds the location of the first non-zero digit within the text
and
uses that to find the starting point for pulling out the text you wanted.
It
uses the MID function to get pull out a sub-string from the main piece of
text. The format of the MID function is...

MID(YourText, StartingPoint, NumberOfCharactersAfterStartingPoint)

We will describe how to get the StartingPoint (the location of the first
non-zero) in a moment; but, once you have it, you wanted that digit along
with the remainder of the text after it. We don't know how many
characters
that will be as the number of leading zeroes can vary; however, there is
no
problem in asking for more characters than exist, so I took a guess that
your text will never be longer than 99 characters (which is why the last
value is 99... it is the 3rd argument of the MID function). Okay, so how
do
we get the StartingPoint. We use ths code...

MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789"))

And how does that work. First off, we note that the FIND function cannot
process an array (values contained within curly braces); however, the MIN
can and so, embedding an array or a function call with an array (even if
that function can't normally handle arrays) within it forces the array to
get processed. This happens be feeding each array element into its
encasing
function (the FIND function in this case) one at a time so that the MIN
function can determine which evaluated value is the smallest. So, the MIN
function is forcing these evaluations to take place...

FIND(1,A1&"123456789")
FIND(2,A1&"123456789")
FIND(3,A1&"123456789")
FIND(4,A1&"123456789")
FIND(5,A1&"123456789")
FIND(6,A1&"123456789")
FIND(7,A1&"123456789")
FIND(8,A1&"123456789")
FIND(9,A1&"123456789")

The returned values from the above are the positions of the digit within
the
text, so when the MIN function looks at all of these values in order to
find
the smallest value, that value is the location in the text... that is,
the
location of the first non-zero digit within the text (which is our
starting
point for the MID function). Okay, now the concatenated "123456789" is
placed there to ensure that FIND will in fact find the digit it is
looking
for at the moment.

Well, that's it. I'm not so sure my explanation is as clear in words as
the
concepts are in my head, but hopefully there is enough for your to fight
your way to an understanding of what is happening.

Rick



Is there **always** digit after the last leading zero (that is, never
something like 000ABC)? If so...

=MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9},A1&"123456789")),99)

I have an item number field which I use a formula to pull the last
segment of the item number field
( such as: WBN-2-FCCG-043-00000094 or WBN-2-IVSG-043-0001A-A )

the results would be a text field such as 00000094 or 0001A-A
or 0230-B

I need to find a way to remove the leading 0's

does anyone have any ideas?




.

Relevant Pages

  • Re: Postroad Mailer message file name algorithm
    ... possible filename characters, 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ. ... The first digit of a *.POP filename is the last digit of the current ... into that array of characters. ...
    (de.comp.os.os2.networking)
  • Re: clear user form after entry
    ... find the first digit that is not a zero ... We don't know how many characters ... process an array; ... location of the first non-zero digit within the text (which is our starting ...
    (microsoft.public.excel.programming)
  • Re: remove leading zeros on a text field
    ... in effect, find the first digit that is not a zero, which is also why I said this formula only applies IF the first character after the leading zeroes is a digit. ... We don't know how many characters that will be as the number of leading zeroes can vary; however, there is no problem in asking for more characters than exist, so I took a guess that your text will never be longer than 99 characters (which is why the last value is 99... ... First off, we note that the FIND function cannot process an array; however, the MIN can and so, embedding an array or a function call with an array within it forces the array to get processed. ...
    (microsoft.public.excel)
  • Re: clear user form after entry
    ... find the first digit that is not a zero ... (notice that the array and listing of digits do not have a zero in them), ... We don't know how many characters ... process an array; ...
    (microsoft.public.excel.programming)
  • Re: clear user form after entry
    ... find the first digit that is not a zero ... (notice that the array and listing of digits do not have a zero in them), ... We don't know how many characters ... process an array; ...
    (microsoft.public.excel.programming)
Loading