remove leading zeros on a text field

[skiing]

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?

thank you for your time and assistance

[Niek Otten]

=VALUE(A1) or, if you want to keep it text, =TEXT(VALUE(A1),"#")

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"skiing" <trp...@chatt.com> wrote in message news:96ffde7d-1fda-47e1...@w7g2000hsa.googlegroups.com...

[skiing]

I tried the Value function and it worked great on the 00000094 like
items -- but on the 0001A-A items the results were #VALUE! --

when I tried the =TEXT(VALUE(A1),"#") it bombed as well due to the
Value(A1) bombing

any other suggestions?

THANKS !

On Apr 29, 1:00 pm, "Niek Otten" <nicol...@xs4all.nl> wrote:
> =VALUE(A1) or, if you want to keep it text, =TEXT(VALUE(A1),"#")
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>

> "skiing" <trpa...@chatt.com> wrote in messagenews:96ffde7d-1fda-47e1...@w7g2000hsa.googlegroups.com...

[@consumerdotorg Bernie Deitrick]

t.r.,

What is the definition of "last segment"? I would think that WBN-2-IVSG-043-0001A-A's last segment
would be "A"...

Anyway, if the formula that you use to extract the last segment is in cell B2 (based on whatever
rules you actually require), then array enter this formula (enter using Ctrl-Shift-Enter) to remove
the leading zeroes:

=MID(B2,MAX((LEFT(B2,ROW(INDIRECT("1:" & LEN(B2))))=REPT("0",ROW(INDIRECT("1:" &
LEN(B2)))))*ROW(INDIRECT("1:" & LEN(B2))))+1,LEN(B2))

HTH,
Bernie
MS Excel MVP

"skiing" <trp...@chatt.com> wrote in message
news:96ffde7d-1fda-47e1...@w7g2000hsa.googlegroups.com...

[@consumerdotorg Bernie Deitrick]

Niek,

That won't work with the trailing letters...

Bernie
MS Excel MVP

"Niek Otten" <nico...@xs4all.nl> wrote in message news:ufGOzphq...@TK2MSFTNGP03.phx.gbl...

[Niek Otten]

Thanks, Bernie,

You're right! I just looked at the first example.........

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message news:eNxkJvhq...@TK2MSFTNGP03.phx.gbl...

[skiing]

Bernie

I do not know how to array enter this formula - I copied and pasted it
and used it in a cell - it did remove the leading 0 on the cell
defined but how do I repeatedly do this?

thank you again - so much !

On Apr 29, 1:09 pm, "Bernie Deitrick" <deitbe @ consumer dot org>
wrote:

> t.r.,
>
> What is the definition of "last segment"?  I would think that WBN-2-IVSG-043-0001A-A's last segment
> would be "A"...
>
> Anyway, if the formula that you use to extract the last segment is in cell B2 (based on whatever
> rules you actually require), then array enter this formula (enter using Ctrl-Shift-Enter) to remove
> the leading zeroes:
>
> =MID(B2,MAX((LEFT(B2,ROW(INDIRECT("1:" & LEN(B2))))=REPT("0",ROW(INDIRECT("1:" &
> LEN(B2)))))*ROW(INDIRECT("1:" & LEN(B2))))+1,LEN(B2))
>
> HTH,
> Bernie
> MS Excel MVP
>

> "skiing" <trpa...@chatt.com> wrote in message

>
> news:96ffde7d-1fda-47e1...@w7g2000hsa.googlegroups.com...
>
>
>
> >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?
>

> > thank you for your time and assistance- Hide quoted text -
>
> - Show quoted text -

[Rick Rothstein (MVP - VB)]

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)

Rick

"skiing" <trp...@chatt.com> wrote in message
news:96ffde7d-1fda-47e1...@w7g2000hsa.googlegroups.com...

[Rick Rothstein (MVP - VB)]

If you can have the situation where only non-digits follow the leading
zeroes, then this formula should work for this general case...

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

Rick

"Rick Rothstein (MVP - VB)" <rick.new...@NO.SPAMverizon.net> wrote in
message news:uMOoKqiq...@TK2MSFTNGP03.phx.gbl...

[Rick Rothstein (MVP - VB)]

Forget this general solution (but if you don't have this situation, still
use my first posted formula)... it won't work if one or more zeroes can
follow the first non-digit.

Rick

"Rick Rothstein (MVP - VB)" <rick.new...@NO.SPAMverizon.net> wrote in

message news:ucj5Iwiq...@TK2MSFTNGP03.phx.gbl...

[skiing]

Wonderful Rick

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

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 can use this constantly - if I understand it

THANK YOU SOOOOO MUCH !

On Apr 29, 2:55 pm, "Rick Rothstein \(MVP - VB\)"

<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> 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)
>
> Rick
>

> "skiing" <trpa...@chatt.com> wrote in message

>
> news:96ffde7d-1fda-47e1...@w7g2000hsa.googlegroups.com...
>
>
>
> >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?
>

[Dave Mills]

Use VBA
Enter in a module:

Function RemoveLeadingZeros(strInput)
RemoveLeadingZeros = strInput
Do While Left(RemoveLeadingZeros, 1) = "0"
RemoveLeadingZeros = Mid(RemoveLeadingZeros, 2)
Loop
End Function

See Excel help "Create your own worksheet functions"

Then in the worksheet
Cell A1 = 00000A
Cell A2.formula = RemoveLeadingZeros(A1)
--
Dave Mills
There are 10 type of people, those that understand binary and those that don't.

[Rick Rothstein (MVP - VB)]

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

[kriste...@gmail.com]

On Tuesday, April 29, 2008 10:00:00 AM UTC-7, Niek Otten wrote:
> =VALUE(A1) or, if you want to keep it text, =TEXT(VALUE(A1),"#")
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>

THANK YOU NIEK!!! It worked! :)

[megan...@gmail.com]

Rick,

This works. Thank you so much. I have been searching for a day and finally a winner.

If it were not for people like you, I would be stuck. Thank you and keep up the support.

Sincerely,
Megan

[a...@landasocial.com]

Hi Rick,

Wondering if you can help!

I am trying to remove the leading zero from a colun of phone numbers, the below formula doesn't work as some phone numbers have more than one 0 in them.

Any ideas?

Thanks!