Re: remove leading zeros on a text field

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(1,A1&"123456789")
FIND(1,A1&"123456789")
FIND(1,A1&"123456789")



> 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?

.

Loading