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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"skiing" <trpayne@chatt.com> wrote in message news:96ffde7d-1fda-47e1-9b44-d3f1e154d799@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

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" <trpayne@chatt.com> wrote in message
news:96ffde7d-1fda-47e1-9b44-d3f1e154d799@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

Niek,

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

Bernie
MS Excel MVP


"Niek Otten" <nicolaus@xs4all.nl> wrote in message news:ufGOzphqIHA.4788@TK2MSFTNGP03.phx.gbl...
> =VALUE(A1) or, if you want to keep it text, =TEXT(VALUE(A1),"#")
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "skiing" <trpayne@chatt.com> wrote in message
> news:96ffde7d-1fda-47e1-9b44-d3f1e154d799@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
>
>

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-9b44-d3f1e154d799@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

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:eNxkJvhqIHA.2292@TK2MSFTNGP03.phx.gbl...
| Niek,
|
| That won't work with the trailing letters...
|
| Bernie
| MS Excel MVP
|
|
| "Niek Otten" <nicolaus@xs4all.nl> wrote in message news:ufGOzphqIHA.4788@TK2MSFTNGP03.phx.gbl...
| > =VALUE(A1) or, if you want to keep it text, =TEXT(VALUE(A1),"#")
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > "skiing" <trpayne@chatt.com> wrote in message
| > news:96ffde7d-1fda-47e1-9b44-d3f1e154d799@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
| >
| >
|
|

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 cellB2 (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-9b44-d3f1e154d799@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 -

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" <trpayne@chatt.com> wrote in message
news:96ffde7d-1fda-47e1-9b44-d3f1e154d799@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

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&"1 23456789")),99),SUBSTITUTE(A1,"0",""))

Rick


"Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:uMOoKqiqIHA.4912@TK2MSFTNGP03.phx.gbl...
> 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" <trpayne@chatt.com> wrote in message
> news:96ffde7d-1fda-47e1-9b44-d3f1e154d799@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
>

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.newsNO.SPAM@NO.SPAMverizon.net> wrote in
message news:ucj5IwiqIHA.4912@TK2MSFTNGP03.phx.gbl...
> 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&"1 23456789")),99),SUBSTITUTE(A1,"0",""))
>
> Rick
>
>
> "Rick Rothstein (MVP - VB)" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in
> message news:uMOoKqiqIHA.4912@TK2MSFTNGP03.phx.gbl...
>> 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" <trpayne@chatt.com> wrote in message
>> news:96ffde7d-1fda-47e1-9b44-d3f1e154d799@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
>>
>