Leading Zero

Need some Help.

Trying to include leading zeros into my inputs.

eg "1234" to display "0001234" and "Sample product" to display
"00Sample0product"

I was able to do this for the numbers but was not able to display
preceeding 00 and inserting 0 for blank space

Further more when I combine the different cells via "CONCATENATE" function I
found the preceeding zeros disappearing.

Can someone please help?

Thanks


Michael

Michael,

Click on the cell that you want to include the zero's in the word. Right
click and go down to format cells. Go to the "number" tab and there will be
a box on the left side. Select Text then click "OK". You should be able to
type zero's in front of your text from that point.

Jeff

"Michael" wrote:

>
> Need some Help.
>
> Trying to include leading zeros into my inputs.
>
> eg "1234" to display "0001234" and "Sample product" to display
> "00Sample0product"
>
> I was able to do this for the numbers but was not able to display
> preceeding 00 and inserting 0 for blank space
>
> Further more when I combine the different cells via "CONCATENATE" function I
> found the preceeding zeros disappearing.
>
> Can someone please help?
>
> Thanks
>
>
> Michael
>
>

Jeffro,

Thanks for your suggestion.
Probably I was not very clear on my intent.
I want to avoid having to manually input "0" for blank spaces and am looking
for a way for Excel to replace this automatically or via some function
changes etc.

ie the inputs coming from third parties come in as text
eg Product="Sample product" part no="123" qty="2"
I need to translate this to
Product= text length 16
part no= text length 6
qty=text length 3
The translated text I am looking for is"Sample0product00000123002"

Hope that this clarifies on my problem/challenge
"Jeffro" wrote:

> Michael,
>
> Click on the cell that you want to include the zero's in the word. Right
> click and go down to format cells. Go to the "number" tab and there will be
> a box on the left side. Select Text then click "OK". You should be able to
> type zero's in front of your text from that point.
>
> Jeff
>
> "Michael" wrote:
>
> >
> > Need some Help.
> >
> > Trying to include leading zeros into my inputs.
> >
> > eg "1234" to display "0001234" and "Sample product" to display
> > "00Sample0product"
> >
> > I was able to do this for the numbers but was not able to display
> > preceeding 00 and inserting 0 for blank space
> >
> > Further more when I combine the different cells via "CONCATENATE" function I
> > found the preceeding zeros disappearing.
> >
> > Can someone please help?
> >
> > Thanks
> >
> >
> > Michael
> >
> >

Hi

Am not sure how you would insert a zero beltween sample
and product but for the others you can try this.

I am just showing how to precede zeroes for qty, in your
example.

Assuming the qty is in C2,

=IF(LEN(C2)=3,C2,IF(LEN(C2)=2,"0"&C2,"00"&C2))

Similarly you can work out for product and part number in
separate cells. Then use concatenate to combine the 3 cells
(one each for formatted product, part and quantity) as
above. It would work fine.


>-----Original Message-----
>Jeffro,
>
>Thanks for your suggestion.
>Probably I was not very clear on my intent.
>I want to avoid having to manually input "0" for blank
spaces and am looking
>for a way for Excel to replace this automatically or via
some function
>changes etc.
>
>ie the inputs coming from third parties come in as text
>eg Product="Sample product" part no="123" qty="2"
>I need to translate this to
>Product= text length 16
>part no= text length 6
>qty=text length 3
>The translated text I am looking for
is"Sample0product00000123002"
>
>Hope that this clarifies on my problem/challenge
>"Jeffro" wrote:
>
>> Michael,
>>
>> Click on the cell that you want to include the zero's
in the word. Right
>> click and go down to format cells. Go to the "number"
tab and there will be
>> a box on the left side. Select Text then click "OK".
You should be able to
>> type zero's in front of your text from that point.
>>
>> Jeff
>>
>> "Michael" wrote:
>>
>> >
>> > Need some Help.
>> >
>> > Trying to include leading zeros into my inputs.
>> >
>> > eg "1234" to display "0001234" and "Sample product"
to display
>> > "00Sample0product"
>> >
>> > I was able to do this for the numbers but was not
able to display
>> > preceeding 00 and inserting 0 for blank space
>> >
>> > Further more when I combine the different cells
via "CONCATENATE" function I
>> > found the preceeding zeros disappearing.
>> >
>> > Can someone please help?
>> >
>> > Thanks
>> >
>> >
>> > Michael
>> >
>> >
>.
>

Hi akk,

It worked, Thank you so much.

Looks like I am still stuck on the insertion of "0" for balnks in a text
field.

Anyhow your advise is mots useful.

Cheers


Michael

"akk" wrote:

> Hi
>
> Am not sure how you would insert a zero beltween sample
> and product but for the others you can try this.
>
> I am just showing how to precede zeroes for qty, in your
> example.
>
> Assuming the qty is in C2,
>
> =IF(LEN(C2)=3,C2,IF(LEN(C2)=2,"0"&C2,"00"&C2))
>
> Similarly you can work out for product and part number in
> separate cells. Then use concatenate to combine the 3 cells
> (one each for formatted product, part and quantity) as
> above. It would work fine.
>
>
> >-----Original Message-----
> >Jeffro,
> >
> >Thanks for your suggestion.
> >Probably I was not very clear on my intent.
> >I want to avoid having to manually input "0" for blank
> spaces and am looking
> >for a way for Excel to replace this automatically or via
> some function
> >changes etc.
> >
> >ie the inputs coming from third parties come in as text
> >eg Product="Sample product" part no="123" qty="2"
> >I need to translate this to
> >Product= text length 16
> >part no= text length 6
> >qty=text length 3
> >The translated text I am looking for
> is"Sample0product00000123002"
> >
> >Hope that this clarifies on my problem/challenge
> >"Jeffro" wrote:
> >
> >> Michael,
> >>
> >> Click on the cell that you want to include the zero's
> in the word. Right
> >> click and go down to format cells. Go to the "number"
> tab and there will be
> >> a box on the left side. Select Text then click "OK".
> You should be able to
> >> type zero's in front of your text from that point.
> >>
> >> Jeff
> >>
> >> "Michael" wrote:
> >>
> >> >
> >> > Need some Help.
> >> >
> >> > Trying to include leading zeros into my inputs.
> >> >
> >> > eg "1234" to display "0001234" and "Sample product"
> to display
> >> > "00Sample0product"
> >> >
> >> > I was able to do this for the numbers but was not
> able to display
> >> > preceeding 00 and inserting 0 for blank space
> >> >
> >> > Further more when I combine the different cells
> via "CONCATENATE" function I
> >> > found the preceeding zeros disappearing.
> >> >
> >> > Can someone please help?
> >> >
> >> > Thanks
> >> >
> >> >
> >> > Michael
> >> >
> >> >
> >.
> >
>

Hi Michael
Have you formatted your text fields as Text. This will make the zero appear.

HTH Michael

"Michael" wrote:

>
> Hi akk,
>
> It worked, Thank you so much.
>
> Looks like I am still stuck on the insertion of "0" for balnks in a text
> field.
>
> Anyhow your advise is mots useful.
>
> Cheers
>
>
> Michael
>
> "akk" wrote:
>
> > Hi
> >
> > Am not sure how you would insert a zero beltween sample
> > and product but for the others you can try this.
> >
> > I am just showing how to precede zeroes for qty, in your
> > example.
> >
> > Assuming the qty is in C2,
> >
> > =IF(LEN(C2)=3,C2,IF(LEN(C2)=2,"0"&C2,"00"&C2))
> >
> > Similarly you can work out for product and part number in
> > separate cells. Then use concatenate to combine the 3 cells
> > (one each for formatted product, part and quantity) as
> > above. It would work fine.
> >
> >
> > >-----Original Message-----
> > >Jeffro,
> > >
> > >Thanks for your suggestion.
> > >Probably I was not very clear on my intent.
> > >I want to avoid having to manually input "0" for blank
> > spaces and am looking
> > >for a way for Excel to replace this automatically or via
> > some function
> > >changes etc.
> > >
> > >ie the inputs coming from third parties come in as text
> > >eg Product="Sample product" part no="123" qty="2"
> > >I need to translate this to
> > >Product= text length 16
> > >part no= text length 6
> > >qty=text length 3
> > >The translated text I am looking for
> > is"Sample0product00000123002"
> > >
> > >Hope that this clarifies on my problem/challenge
> > >"Jeffro" wrote:
> > >
> > >> Michael,
> > >>
> > >> Click on the cell that you want to include the zero's
> > in the word. Right
> > >> click and go down to format cells. Go to the "number"
> > tab and there will be
> > >> a box on the left side. Select Text then click "OK".
> > You should be able to
> > >> type zero's in front of your text from that point.
> > >>
> > >> Jeff
> > >>
> > >> "Michael" wrote:
> > >>
> > >> >
> > >> > Need some Help.
> > >> >
> > >> > Trying to include leading zeros into my inputs.
> > >> >
> > >> > eg "1234" to display "0001234" and "Sample product"
> > to display
> > >> > "00Sample0product"
> > >> >
> > >> > I was able to do this for the numbers but was not
> > able to display
> > >> > preceeding 00 and inserting 0 for blank space
> > >> >
> > >> > Further more when I combine the different cells
> > via "CONCATENATE" function I
> > >> > found the preceeding zeros disappearing.
> > >> >
> > >> > Can someone please help?
> > >> >
> > >> > Thanks
> > >> >
> > >> >
> > >> > Michael
> > >> >
> > >> >
> > >.
> > >
> >

Michael

Another idea has struct. Use Data - Text to columns
feature to split the "product" column into 2 (result will
be sample in one column and product in another column).
Then while using concatenate, you can insert the zero
between the 2 words.

>-----Original Message-----
>Hi Michael
>Have you formatted your text fields as Text. This will
make the zero appear.
>
>HTH Michael
>
>"Michael" wrote:
>
>>
>> Hi akk,
>>
>> It worked, Thank you so much.
>>
>> Looks like I am still stuck on the insertion of "0" for
balnks in a text
>> field.
>>
>> Anyhow your advise is mots useful.
>>
>> Cheers
>>
>>
>> Michael
>>
>> "akk" wrote:
>>
>> > Hi
>> >
>> > Am not sure how you would insert a zero beltween
sample
>> > and product but for the others you can try this.
>> >
>> > I am just showing how to precede zeroes for qty, in
your
>> > example.
>> >
>> > Assuming the qty is in C2,
>> >
>> > =IF(LEN(C2)=3,C2,IF(LEN(C2)=2,"0"&C2,"00"&C2))
>> >
>> > Similarly you can work out for product and part
number in
>> > separate cells. Then use concatenate to combine the 3
cells
>> > (one each for formatted product, part and quantity)
as
>> > above. It would work fine.
>> >
>> >
>> > >-----Original Message-----
>> > >Jeffro,
>> > >
>> > >Thanks for your suggestion.
>> > >Probably I was not very clear on my intent.
>> > >I want to avoid having to manually input "0" for
blank
>> > spaces and am looking
>> > >for a way for Excel to replace this automatically or
via
>> > some function
>> > >changes etc.
>> > >
>> > >ie the inputs coming from third parties come in as
text
>> > >eg Product="Sample product" part no="123" qty="2"
>> > >I need to translate this to
>> > >Product= text length 16
>> > >part no= text length 6
>> > >qty=text length 3
>> > >The translated text I am looking for
>> > is"Sample0product00000123002"
>> > >
>> > >Hope that this clarifies on my problem/challenge
>> > >"Jeffro" wrote:
>> > >
>> > >> Michael,
>> > >>
>> > >> Click on the cell that you want to include the
zero's
>> > in the word. Right
>> > >> click and go down to format cells. Go to
the "number"
>> > tab and there will be
>> > >> a box on the left side. Select Text then
click "OK".
>> > You should be able to
>> > >> type zero's in front of your text from that point.
>> > >>
>> > >> Jeff
>> > >>
>> > >> "Michael" wrote:
>> > >>
>> > >> >
>> > >> > Need some Help.
>> > >> >
>> > >> > Trying to include leading zeros into my inputs.
>> > >> >
>> > >> > eg "1234" to display "0001234" and "Sample
product"
>> > to display
>> > >> > "00Sample0product"
>> > >> >
>> > >> > I was able to do this for the numbers but was
not
>> > able to display
>> > >> > preceeding 00 and inserting 0 for blank space
>> > >> >
>> > >> > Further more when I combine the different cells
>> > via "CONCATENATE" function I
>> > >> > found the preceeding zeros disappearing.
>> > >> >
>> > >> > Can someone please help?
>> > >> >
>> > >> > Thanks
>> > >> >
>> > >> >
>> > >> > Michael
>> > >> >
>> > >> >
>> > >.
>> > >
>> >
>.
>

Michael;
Try this;
(assume Prod Descript is in A2,Part # is in B2 and Quant is in C2)

In helper cells
Product Descr not sure if the two inst. can be combined so will use two helper cells D2 &
E2
D2 =right(" "&A2,16)
E2 =substitute(D2," ","0")

Part #
F2 =right("000000"&B2,6)
Quant
G2 =right("000"&C2,3)

To combine Prod Desc, Prod # and Quant
=E2&F2&G2
--
Regards;
Rob
------------------------------------------------------------------------
"Michael" <Michael@discussions.microsoft.com> wrote in message
news:28FD6193-74B2-40BD-A37C-4DEC2F8401BE@microsoft.com...
> Jeffro,
>
> Thanks for your suggestion.
> Probably I was not very clear on my intent.
> I want to avoid having to manually input "0" for blank spaces and am looking
> for a way for Excel to replace this automatically or via some function
> changes etc.
>
> ie the inputs coming from third parties come in as text
> eg Product="Sample product" part no="123" qty="2"
> I need to translate this to
> Product= text length 16
> part no= text length 6
> qty=text length 3
> The translated text I am looking for is"Sample0product00000123002"
>
> Hope that this clarifies on my problem/challenge
> "Jeffro" wrote:
>
> > Michael,
> >
> > Click on the cell that you want to include the zero's in the word. Right
> > click and go down to format cells. Go to the "number" tab and there will be
> > a box on the left side. Select Text then click "OK". You should be able to
> > type zero's in front of your text from that point.
> >
> > Jeff
> >
> > "Michael" wrote:
> >
> > >
> > > Need some Help.
> > >
> > > Trying to include leading zeros into my inputs.
> > >
> > > eg "1234" to display "0001234" and "Sample product" to display
> > > "00Sample0product"
> > >
> > > I was able to do this for the numbers but was not able to display
> > > preceeding 00 and inserting 0 for blank space
> > >
> > > Further more when I combine the different cells via "CONCATENATE" function I
> > > found the preceeding zeros disappearing.
> > >
> > > Can someone please help?
> > >
> > > Thanks
> > >
> > >
> > > Michael
> > >
> > >

Hi akk,

Good suggestion, unfortunately cannot be applied in my situation since the
text is a descriptive input defining the product concern and is provided as
an external input.

regardless thanks for the idea.

Cheers

"akk" wrote:

> Michael
>
> Another idea has struct. Use Data - Text to columns
> feature to split the "product" column into 2 (result will
> be sample in one column and product in another column).
> Then while using concatenate, you can insert the zero
> between the 2 words.
>
> >-----Original Message-----
> >Hi Michael
> >Have you formatted your text fields as Text. This will
> make the zero appear.
> >
> >HTH Michael
> >
> >"Michael" wrote:
> >
> >>
> >> Hi akk,
> >>
> >> It worked, Thank you so much.
> >>
> >> Looks like I am still stuck on the insertion of "0" for
> balnks in a text
> >> field.
> >>
> >> Anyhow your advise is mots useful.
> >>
> >> Cheers
> >>
> >>
> >> Michael
> >>
> >> "akk" wrote:
> >>
> >> > Hi
> >> >
> >> > Am not sure how you would insert a zero beltween
> sample
> >> > and product but for the others you can try this.
> >> >
> >> > I am just showing how to precede zeroes for qty, in
> your
> >> > example.
> >> >
> >> > Assuming the qty is in C2,
> >> >
> >> > =IF(LEN(C2)=3,C2,IF(LEN(C2)=2,"0"&C2,"00"&C2))
> >> >
> >> > Similarly you can work out for product and part
> number in
> >> > separate cells. Then use concatenate to combine the 3
> cells
> >> > (one each for formatted product, part and quantity)
> as
> >> > above. It would work fine.
> >> >
> >> >
> >> > >-----Original Message-----
> >> > >Jeffro,
> >> > >
> >> > >Thanks for your suggestion.
> >> > >Probably I was not very clear on my intent.
> >> > >I want to avoid having to manually input "0" for
> blank
> >> > spaces and am looking
> >> > >for a way for Excel to replace this automatically or
> via
> >> > some function
> >> > >changes etc.
> >> > >
> >> > >ie the inputs coming from third parties come in as
> text
> >> > >eg Product="Sample product" part no="123" qty="2"
> >> > >I need to translate this to
> >> > >Product= text length 16
> >> > >part no= text length 6
> >> > >qty=text length 3
> >> > >The translated text I am looking for
> >> > is"Sample0product00000123002"
> >> > >
> >> > >Hope that this clarifies on my problem/challenge
> >> > >"Jeffro" wrote:
> >> > >
> >> > >> Michael,
> >> > >>
> >> > >> Click on the cell that you want to include the
> zero's
> >> > in the word. Right
> >> > >> click and go down to format cells. Go to
> the "number"
> >> > tab and there will be
> >> > >> a box on the left side. Select Text then
> click "OK".
> >> > You should be able to
> >> > >> type zero's in front of your text from that point.
> >> > >>
> >> > >> Jeff
> >> > >>
> >> > >> "Michael" wrote:
> >> > >>
> >> > >> >
> >> > >> > Need some Help.
> >> > >> >
> >> > >> > Trying to include leading zeros into my inputs.
> >> > >> >
> >> > >> > eg "1234" to display "0001234" and "Sample
> product"
> >> > to display
> >> > >> > "00Sample0product"
> >> > >> >
> >> > >> > I was able to do this for the numbers but was
> not
> >> > able to display
> >> > >> > preceeding 00 and inserting 0 for blank space
> >> > >> >
> >> > >> > Further more when I combine the different cells
> >> > via "CONCATENATE" function I
> >> > >> > found the preceeding zeros disappearing.
> >> > >> >
> >> > >> > Can someone please help?
> >> > >> >
> >> > >> > Thanks
> >> > >> >
> >> > >> >
> >> > >> > Michael
> >> > >> >
> >> > >> >
> >> > >.
> >> > >
> >> >
> >.
> >
>

in that just concatenate with the blanks and then
use "Find and replace" function to find a blank and
replace with a 0.

hope this would solve the problem

>-----Original Message-----
>
>Hi akk,
>
>Good suggestion, unfortunately cannot be applied in my
situation since the
>text is a descriptive input defining the product concern
and is provided as
>an external input.
>
>regardless thanks for the idea.
>
>Cheers
>
>"akk" wrote:
>
>> Michael
>>
>> Another idea has struct. Use Data - Text to columns
>> feature to split the "product" column into 2 (result
will
>> be sample in one column and product in another column).
>> Then while using concatenate, you can insert the zero
>> between the 2 words.
>>
>> >-----Original Message-----
>> >Hi Michael
>> >Have you formatted your text fields as Text. This will
>> make the zero appear.
>> >
>> >HTH Michael
>> >
>> >"Michael" wrote:
>> >
>> >>
>> >> Hi akk,
>> >>
>> >> It worked, Thank you so much.
>> >>
>> >> Looks like I am still stuck on the insertion of "0"
for
>> balnks in a text
>> >> field.
>> >>
>> >> Anyhow your advise is mots useful.
>> >>
>> >> Cheers
>> >>
>> >>
>> >> Michael
>> >>
>> >> "akk" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > Am not sure how you would insert a zero beltween
>> sample
>> >> > and product but for the others you can try this.
>> >> >
>> >> > I am just showing how to precede zeroes for qty,
in
>> your
>> >> > example.
>> >> >
>> >> > Assuming the qty is in C2,
>> >> >
>> >> > =IF(LEN(C2)=3,C2,IF(LEN(C2)=2,"0"&C2,"00"&C2))
>> >> >
>> >> > Similarly you can work out for product and part
>> number in
>> >> > separate cells. Then use concatenate to combine
the 3
>> cells
>> >> > (one each for formatted product, part and
quantity)
>> as
>> >> > above. It would work fine.
>> >> >
>> >> >
>> >> > >-----Original Message-----
>> >> > >Jeffro,
>> >> > >
>> >> > >Thanks for your suggestion.
>> >> > >Probably I was not very clear on my intent.
>> >> > >I want to avoid having to manually input "0" for
>> blank
>> >> > spaces and am looking
>> >> > >for a way for Excel to replace this automatically
or
>> via
>> >> > some function
>> >> > >changes etc.
>> >> > >
>> >> > >ie the inputs coming from third parties come in
as
>> text
>> >> > >eg Product="Sample product" part no="123" qty="2"
>> >> > >I need to translate this to
>> >> > >Product= text length 16
>> >> > >part no= text length 6
>> >> > >qty=text length 3
>> >> > >The translated text I am looking for
>> >> > is"Sample0product00000123002"
>> >> > >
>> >> > >Hope that this clarifies on my problem/challenge
>> >> > >"Jeffro" wrote:
>> >> > >
>> >> > >> Michael,
>> >> > >>
>> >> > >> Click on the cell that you want to include the
>> zero's
>> >> > in the word. Right
>> >> > >> click and go down to format cells. Go to
>> the "number"
>> >> > tab and there will be
>> >> > >> a box on the left side. Select Text then
>> click "OK".
>> >> > You should be able to
>> >> > >> type zero's in front of your text from that
point.
>> >> > >>
>> >> > >> Jeff
>> >> > >>
>> >> > >> "Michael" wrote:
>> >> > >>
>> >> > >> >
>> >> > >> > Need some Help.
>> >> > >> >
>> >> > >> > Trying to include leading zeros into my
inputs.
>> >> > >> >
>> >> > >> > eg "1234" to display "0001234" and "Sample
>> product"
>> >> > to display
>> >> > >> > "00Sample0product"
>> >> > >> >
>> >> > >> > I was able to do this for the numbers but
was
>> not
>> >> > able to display
>> >> > >> > preceeding 00 and inserting 0 for blank
space
>> >> > >> >
>> >> > >> > Further more when I combine the different
cells
>> >> > via "CONCATENATE" function I
>> >> > >> > found the preceeding zeros disappearing.
>> >> > >> >
>> >> > >> > Can someone please help?
>> >> > >> >
>> >> > >> > Thanks
>> >> > >> >
>> >> > >> >
>> >> > >> > Michael
>> >> > >> >
>> >> > >> >
>> >> > >.
>> >> > >
>> >> >
>> >.
>> >
>>
>.
>

RWN,

Looks like your solution will solve my problem on replacing "0" for blanks
on the text cells.

Thank you for your help.

I really find this forum most useful as the participants are not only
knowledgeable but extremely helpful.

I will be tuning to this forum to hone my skills to better optimise the
useage of MS Excel.

Cheers

Michael

"RWN" wrote:

> Michael;
> Try this;
> (assume Prod Descript is in A2,Part # is in B2 and Quant is in C2)
>
> In helper cells
> Product Descr not sure if the two inst. can be combined so will use two helper cells D2 &
> E2
> D2 =right(" "&A2,16)
> E2 =substitute(D2," ","0")
>
> Part #
> F2 =right("000000"&B2,6)
> Quant
> G2 =right("000"&C2,3)
>
> To combine Prod Desc, Prod # and Quant
> =E2&F2&G2
> --
> Regards;
> Rob
> ------------------------------------------------------------------------
> "Michael" <Michael@discussions.microsoft.com> wrote in message
> news:28FD6193-74B2-40BD-A37C-4DEC2F8401BE@microsoft.com...
> > Jeffro,
> >
> > Thanks for your suggestion.
> > Probably I was not very clear on my intent.
> > I want to avoid having to manually input "0" for blank spaces and am looking
> > for a way for Excel to replace this automatically or via some function
> > changes etc.
> >
> > ie the inputs coming from third parties come in as text
> > eg Product="Sample product" part no="123" qty="2"
> > I need to translate this to
> > Product= text length 16
> > part no= text length 6
> > qty=text length 3
> > The translated text I am looking for is"Sample0product00000123002"
> >
> > Hope that this clarifies on my problem/challenge
> > "Jeffro" wrote:
> >
> > > Michael,
> > >
> > > Click on the cell that you want to include the zero's in the word. Right
> > > click and go down to format cells. Go to the "number" tab and there will be
> > > a box on the left side. Select Text then click "OK". You should be able to
> > > type zero's in front of your text from that point.
> > >
> > > Jeff
> > >
> > > "Michael" wrote:
> > >
> > > >
> > > > Need some Help.
> > > >
> > > > Trying to include leading zeros into my inputs.
> > > >
> > > > eg "1234" to display "0001234" and "Sample product" to display
> > > > "00Sample0product"
> > > >
> > > > I was able to do this for the numbers but was not able to display
> > > > preceeding 00 and inserting 0 for blank space
> > > >
> > > > Further more when I combine the different cells via "CONCATENATE" function I
> > > > found the preceeding zeros disappearing.
> > > >
> > > > Can someone please help?
> > > >
> > > > Thanks
> > > >
> > > >
> > > > Michael
> > > >
> > > >
>
>
>

No doubt about it, this, as well as the other Office product forms, will teach you a lot.
(not the least of which is that there are about 100 ways to do any given task!)

Good luck.
--
Regards;
Rob
------------------------------------------------------------------------
"Michael" <Michael@discussions.microsoft.com> wrote in message
news:D94E912C-C1F6-453E-8703-88521054CCCB@microsoft.com...
> RWN,
>
> Looks like your solution will solve my problem on replacing "0" for blanks
> on the text cells.
>
> Thank you for your help.
>
> I really find this forum most useful as the participants are not only
> knowledgeable but extremely helpful.
>
> I will be tuning to this forum to hone my skills to better optimise the
> useage of MS Excel.
>
> Cheers
>
> Michael
>
> "RWN" wrote:
>
> > Michael;
> > Try this;
> > (assume Prod Descript is in A2,Part # is in B2 and Quant is in C2)
> >
> > In helper cells
> > Product Descr not sure if the two inst. can be combined so will use two helper cells
D2 &
> > E2
> > D2 =right(" "&A2,16)
> > E2 =substitute(D2," ","0")
> >
> > Part #
> > F2 =right("000000"&B2,6)
> > Quant
> > G2 =right("000"&C2,3)
> >
> > To combine Prod Desc, Prod # and Quant
> > =E2&F2&G2
> > --
> > Regards;
> > Rob
> > ------------------------------------------------------------------------
> > "Michael" <Michael@discussions.microsoft.com> wrote in message
> > news:28FD6193-74B2-40BD-A37C-4DEC2F8401BE@microsoft.com...
> > > Jeffro,
> > >
> > > Thanks for your suggestion.
> > > Probably I was not very clear on my intent.
> > > I want to avoid having to manually input "0" for blank spaces and am looking
> > > for a way for Excel to replace this automatically or via some function
> > > changes etc.
> > >
> > > ie the inputs coming from third parties come in as text
> > > eg Product="Sample product" part no="123" qty="2"
> > > I need to translate this to
> > > Product= text length 16
> > > part no= text length 6
> > > qty=text length 3
> > > The translated text I am looking for is"Sample0product00000123002"
> > >
> > > Hope that this clarifies on my problem/challenge
> > > "Jeffro" wrote:
> > >
> > > > Michael,
> > > >
> > > > Click on the cell that you want to include the zero's in the word. Right
> > > > click and go down to format cells. Go to the "number" tab and there will be
> > > > a box on the left side. Select Text then click "OK". You should be able to
> > > > type zero's in front of your text from that point.
> > > >
> > > > Jeff
> > > >
> > > > "Michael" wrote:
> > > >
> > > > >
> > > > > Need some Help.
> > > > >
> > > > > Trying to include leading zeros into my inputs.
> > > > >
> > > > > eg "1234" to display "0001234" and "Sample product" to display
> > > > > "00Sample0product"
> > > > >
> > > > > I was able to do this for the numbers but was not able to display
> > > > > preceeding 00 and inserting 0 for blank space
> > > > >
> > > > > Further more when I combine the different cells via "CONCATENATE" function I
> > > > > found the preceeding zeros disappearing.
> > > > >
> > > > > Can someone please help?
> > > > >
> > > > > Thanks
> > > > >
> > > > >
> > > > > Michael
> > > > >
> > > > >
> >
> >
> >

Hi akk,

Your suggestion around using Len() has a limit, could only repeat the IF
command 7 times. Any idea on how to go around this if I need to carrry out
your suggestion on a cell with a length of say 16 spaces?

Regards


Michael


> >> >
> >> >"Michael" wrote:
> >> >
> >> >>
> >> >> Hi akk,
> >> >>
> >> >> It worked, Thank you so much.
> >> >>
> >> >> Looks like I am still stuck on the insertion of "0"
> for
> >> balnks in a text
> >> >> field.
> >> >>
> >> >> Anyhow your advise is mots useful.
> >> >>
> >> >> Cheers
> >> >>
> >> >>
> >> >> Michael
> >> >>
> >> >> "akk" wrote:
> >> >>
> >> >> > Hi
> >> >> >
> >> >> > Am not sure how you would insert a zero beltween
> >> sample
> >> >> > and product but for the others you can try this.
> >> >> >
> >> >> > I am just showing how to precede zeroes for qty,
> in
> >> your
> >> >> > example.
> >> >> >
> >> >> > Assuming the qty is in C2,
> >> >> >
> >> >> > =IF(LEN(C2)=3,C2,IF(LEN(C2)=2,"0"&C2,"00"&C2))
> >> >> >
> >> >> > Similarly you can work out for product and part
> >> number in
> >> >> > separate cells. Then use concatenate to combine
> the 3
> >> cells
> >> >> > (one each for formatted product, part and
> quantity)
> >> as
> >> >> > above. It would work fine.
> >> >> >
> >> >> >
> >> >> > >-----Original Message-----
> >> >> > >Jeffro,
> >> >> > >
> >> >> > >Thanks for your suggestion.
> >> >> > >Probably I was not very clear on my intent.
> >> >> > >I want to avoid having to manually input "0" for
> >> blank
> >> >> > spaces and am looking
> >> >> > >for a way for Excel to replace this automatically
> or
> >> via
> >> >> > some function
> >> >> > >changes etc.
> >> >> > >
> >> >> > >ie the inputs coming from third parties come in
> as
> >> text
> >> >> > >eg Product="Sample product" part no="123" qty="2"
> >> >> > >I need to translate this to
> >> >> > >Product= text length 16
> >> >> > >part no= text length 6
> >> >> > >qty=text length 3
> >> >> > >The translated text I am looking for
> >> >> > is"Sample0product00000123002"
> >> >> > >
> >> >> > >Hope that this clarifies on my problem/challenge
> >> >> > >"Jeffro" wrote:
> >> >> > >
> >> >> > >> Michael,
> >> >> > >>
> >> >> > >> Click on the cell that you want to include the
> >> zero's
> >> >> > in the word. Right
> >> >> > >> click and go down to format cells. Go to
> >> the "number"
> >> >> > tab and there will be
> >> >> > >> a box on the left side. Select Text then
> >> click "OK".
> >> >> > You should be able to
> >> >> > >> type zero's in front of your text from that
> point.
> >> >> > >>
> >> >> > >> Jeff
> >> >> > >>
> >> >> > >> "Michael" wrote:
> >> >> > >>
> >> >> > >> >
> >> >> > >> > Need some Help.
> >> >> > >> >
> >> >> > >> > Trying to include leading zeros into my
> inputs.
> >> >> > >> >
> >> >> > >> > eg "1234" to display "0001234" and "Sample
> >> product"
> >> >> > to display
> >> >> > >> > "00Sample0product"
> >> >> > >> >
> >> >> > >> > I was able to do this for the numbers but
> was
> >> not
> >> >> > able to display
> >> >> > >> > preceeding 00 and inserting 0 for blank
> space
> >> >> > >> >
> >> >> > >> > Further more when I combine the different
> cells
> >> >> > via "CONCATENATE" function I
> >> >> > >> > found the preceeding zeros disappearing.
> >> >> > >> >
> >> >> > >> > Can someone please help?
> >> >> > >> >
> >> >> > >> > Thanks
> >> >> > >> >
> >> >> > >> >
> >> >> > >> > Michael
> >> >> > >> >
> >> >> > >> >
> >> >> > >.
> >> >> > >
> >> >> >
> >> >.
> >> >
> >>
> >.
> >
>

Michael,

Check this out

http://www.cpearson.com/excel/nested.htm

Rgds

>-----Original Message-----
>Hi akk,
>
>Your suggestion around using Len() has a limit, could
only repeat the IF
>command 7 times. Any idea on how to go around this if I
need to carrry out
>your suggestion on a cell with a length of say 16 spaces?
>
>Regards
>
>
>Michael
>
>
>> >> >
>> >> >"Michael" wrote:
>> >> >
>> >> >>
>> >> >> Hi akk,
>> >> >>
>> >> >> It worked, Thank you so much.
>> >> >>
>> >> >> Looks like I am still stuck on the insertion
of "0"
>> for
>> >> balnks in a text
>> >> >> field.
>> >> >>
>> >> >> Anyhow your advise is mots useful.
>> >> >>
>> >> >> Cheers
>> >> >>
>> >> >>
>> >> >> Michael
>> >> >>
>> >> >> "akk" wrote:
>> >> >>
>> >> >> > Hi
>> >> >> >
>> >> >> > Am not sure how you would insert a zero
beltween
>> >> sample
>> >> >> > and product but for the others you can try this.
>> >> >> >
>> >> >> > I am just showing how to precede zeroes for
qty,
>> in
>> >> your
>> >> >> > example.
>> >> >> >
>> >> >> > Assuming the qty is in C2,
>> >> >> >
>> >> >> > =IF(LEN(C2)=3,C2,IF(LEN(C2)=2,"0"&C2,"00"&C2))
>> >> >> >
>> >> >> > Similarly you can work out for product and part
>> >> number in
>> >> >> > separate cells. Then use concatenate to combine
>> the 3
>> >> cells
>> >> >> > (one each for formatted product, part and
>> quantity)
>> >> as
>> >> >> > above. It would work fine.
>> >> >> >
>> >> >> >
>> >> >> > >-----Original Message-----
>> >> >> > >Jeffro,
>> >> >> > >
>> >> >> > >Thanks for your suggestion.
>> >> >> > >Probably I was not very clear on my intent.
>> >> >> > >I want to avoid having to manually input "0"
for
>> >> blank
>> >> >> > spaces and am looking
>> >> >> > >for a way for Excel to replace this
automatically
>> or
>> >> via
>> >> >> > some function
>> >> >> > >changes etc.
>> >> >> > >
>> >> >> > >ie the inputs coming from third parties come
in
>> as
>> >> text
>> >> >> > >eg Product="Sample product" part no="123"
qty="2"
>> >> >> > >I need to translate this to
>> >> >> > >Product= text length 16
>> >> >> > >part no= text length 6
>> >> >> > >qty=text length 3
>> >> >> > >The translated text I am looking for
>> >> >> > is"Sample0product00000123002"
>> >> >> > >
>> >> >> > >Hope that this clarifies on my
problem/challenge
>> >> >> > >"Jeffro" wrote:
>> >> >> > >
>> >> >> > >> Michael,
>> >> >> > >>
>> >> >> > >> Click on the cell that you want to include
the
>> >> zero's
>> >> >> > in the word. Right
>> >> >> > >> click and go down to format cells. Go to
>> >> the "number"
>> >> >> > tab and there will be
>> >> >> > >> a box on the left side. Select Text then
>> >> click "OK".
>> >> >> > You should be able to
>> >> >> > >> type zero's in front of your text from that
>> point.
>> >> >> > >>
>> >> >> > >> Jeff
>> >> >> > >>
>> >> >> > >> "Michael" wrote:
>> >> >> > >>
>> >> >> > >> >
>> >> >> > >> > Need some Help.
>> >> >> > >> >
>> >> >> > >> > Trying to include leading zeros into my
>> inputs.
>> >> >> > >> >
>> >> >> > >> > eg "1234" to display "0001234" and "Sample
>> >> product"
>> >> >> > to display
>> >> >> > >> > "00Sample0product"
>> >> >> > >> >
>> >> >> > >> > I was able to do this for the numbers but
>> was
>> >> not
>> >> >> > able to display
>> >> >> > >> > preceeding 00 and inserting 0 for blank
>> space
>> >> >> > >> >
>> >> >> > >> > Further more when I combine the different
>> cells
>> >> >> > via "CONCATENATE" function I
>> >> >> > >> > found the preceeding zeros disappearing.
>> >> >> > >> >
>> >> >> > >> > Can someone please help?
>> >> >> > >> >
>> >> >> > >> > Thanks
>> >> >> > >> >
>> >> >> > >> >
>> >> >> > >> > Michael
>> >> >> > >> >
>> >> >> > >> >
>> >> >> > >.
>> >> >> > >
>> >> >> >
>> >> >.
>> >> >
>> >>
>> >.
>> >
>>
>.
>

I see you got your answer, but sometimes writing complex "IF" statements can get out of hand. Another way is to create a table ... let's say you have to have at least 8 characters for your number, with leading zeros as necessary. Table would look like this:

Len Zeros
0 00000000
1 0000000
2 000000
3 00000
4 0000
5 000
6 00
7 0
8

And call it TBL1

On the data worksheet, assume cell A2 contains your first number ... it contains, say, 856. In cell B2 write:

=VLOOKUP(LEN(A2),TBL1,2,0)&A2
Response will be: "00000856"

Then copy B2 down, turn to values, replace column "A", and you're done.

The IF method is perfrctly fine, of course, but the above avoids having to think about the conditions and arguments when the numbers get big.

Mark

akk

Thanks for the pointer.

Regards


Michael
"akk" wrote:

> Michael,
>
> Check this out
>
> http://www.cpearson.com/excel/nested.htm
>
> Rgds
>
> >-----Original Message-----
> >Hi akk,
> >
> >Your suggestion around using Len() has a limit, could
> only repeat the IF
> >command 7 times. Any idea on how to go around this if I
> need to carrry out
> >your suggestion on a cell with a length of say 16 spaces?
> >
> >Regards
> >
> >
> >Michael
> >
> >
> >> >> >
> >> >> >"Michael" wrote:
> >> >> >
> >> >> >>
> >> >> >> Hi akk,
> >> >> >>
> >> >> >> It worked, Thank you so much.
> >> >> >>
> >> >> >> Looks like I am still stuck on the insertion
> of "0"
> >> for
> >> >> balnks in a text
> >> >> >> field.
> >> >> >>
> >> >> >> Anyhow your advise is mots useful.
> >> >> >>
> >> >> >> Cheers
> >> >> >>
> >> >> >>
> >> >> >> Michael
> >> >> >>
> >> >> >> "akk" wrote:
> >> >> >>
> >> >> >> > Hi
> >> >> >> >
> >> >> >> > Am not sure how you would insert a zero
> beltween
> >> >> sample
> >> >> >> > and product but for the others you can try this.
> >> >> >> >
> >> >> >> > I am just showing how to precede zeroes for
> qty,
> >> in
> >> >> your
> >> >> >> > example.
> >> >> >> >
> >> >> >> > Assuming the qty is in C2,
> >> >> >> >
> >> >> >> > =IF(LEN(C2)=3,C2,IF(LEN(C2)=2,"0"&C2,"00"&C2))
> >> >> >> >
> >> >> >> > Similarly you can work out for product and part
> >> >> number in
> >> >> >> > separate cells. Then use concatenate to combine
> >> the 3
> >> >> cells
> >> >> >> > (one each for formatted product, part and
> >> quantity)
> >> >> as
> >> >> >> > above. It would work fine.
> >> >> >> >
> >> >> >> >
> >> >> >> > >-----Original Message-----
> >> >> >> > >Jeffro,
> >> >> >> > >
> >> >> >> > >Thanks for your suggestion.
> >> >> >> > >Probably I was not very clear on my intent.
> >> >> >> > >I want to avoid having to manually input "0"
> for
> >> >> blank
> >> >> >> > spaces and am looking
> >> >> >> > >for a way for Excel to replace this
> automatically
> >> or
> >> >> via
> >> >> >> > some function
> >> >> >> > >changes etc.
> >> >> >> > >
> >> >> >> > >ie the inputs coming from third parties come
> in
> >> as
> >> >> text
> >> >> >> > >eg Product="Sample product" part no="123"
> qty="2"
> >> >> >> > >I need to translate this to
> >> >> >> > >Product= text length 16
> >> >> >> > >part no= text length 6
> >> >> >> > >qty=text length 3
> >> >> >> > >The translated text I am looking for
> >> >> >> > is"Sample0product00000123002"
> >> >> >> > >
> >> >> >> > >Hope that this clarifies on my
> problem/challenge
> >> >> >> > >"Jeffro" wrote:
> >> >> >> > >
> >> >> >> > >> Michael,
> >> >> >> > >>
> >> >> >> > >> Click on the cell that you want to include
> the
> >> >> zero's
> >> >> >> > in the word. Right
> >> >> >> > >> click and go down to format cells. Go to
> >> >> the "number"
> >> >> >> > tab and there will be
> >> >> >> > >> a box on the left side. Select Text then
> >> >> click "OK".
> >> >> >> > You should be able to
> >> >> >> > >> type zero's in front of your text from that
> >> point.
> >> >> >> > >>
> >> >> >> > >> Jeff
> >> >> >> > >>
> >> >> >> > >> "Michael" wrote:
> >> >> >> > >>
> >> >> >> > >> >
> >> >> >> > >> > Need some Help.
> >> >> >> > >> >
> >> >> >> > >> > Trying to include leading zeros into my
> >> inputs.
> >> >> >> > >> >
> >> >> >> > >> > eg "1234" to display "0001234" and "Sample
> >> >> product"
> >> >> >> > to display
> >> >> >> > >> > "00Sample0product"
> >> >> >> > >> >
> >> >> >> > >> > I was able to do this for the numbers but
> >> was
> >> >> not
> >> >> >> > able to display
> >> >> >> > >> > preceeding 00 and inserting 0 for blank
> >> space
> >> >> >> > >> >
> >> >> >> > >> > Further more when I combine the different
> >> cells
> >> >> >> > via "CONCATENATE" function I
> >> >> >> > >> > found the preceeding zeros disappearing.
> >> >> >> > >> >
> >> >> >> > >> > Can someone please help?
> >> >> >> > >> >
> >> >> >> > >> > Thanks
> >> >> >> > >> >
> >> >> >> > >> >
> >> >> >> > >> > Michael
> >> >> >> > >> >
> >> >> >> > >> >
> >> >> >> > >.
> >> >> >> > >
> >> >> >> >
> >> >> >.
> >> >> >
> >> >>
> >> >.
> >> >
> >>
> >.
> >
>