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;
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" wrote in 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" wrote in 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

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" wrote in message
...
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" wrote in 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