Re: clear user form after entry

*** 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: Display dollar values?
    ... If the width of the two characters is ... it was missing the left most digit. ... no indication that anything is amiss. ... If the cents amount doesn't end in a zero, ...
    (microsoft.public.access.reports)
  • Re: clear user form after entry
    ... Sorry about any confusion these two posts may have caused... ... find the first digit that is not a zero ... problem in asking for more characters than exist, so I took a guess that ... process an array; ...
    (microsoft.public.excel.programming)
  • 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: 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