I have a column of numbers with lead zeros, formatted as Numer PESEL. I need
to get these numbers into text format, but keep the lead zeros and I can't
figure out how.

When I double click on the cell, the zeros disappear. If I copy and paste
to a text formatted cell, the zeros disappear. Any suggestions?

Use this formula to convert to TEXT, then copy PasteSpecial Values to get rid
of the formulas.........(use however many zeros you need).....

=TEXT(A1,"0000000")

Vaya con Dios,
Chuck, CABGx3





"Laury" wrote:

I have a column of numbers with lead zeros, formatted as Numer PESEL. I need
to get these numbers into text format, but keep the lead zeros and I can't
figure out how.

When I double click on the cell, the zeros disappear. If I copy and paste
to a text formatted cell, the zeros disappear. Any suggestions?

Didn't work for me. I put in the formula and 00003014150 from the orig cell
changed to 3014150, I then copied and pasted as value and still the cell
showed 3014150. ???


"CLR" wrote:

Use this formula to convert to TEXT, then copy PasteSpecial Values to get rid
of the formulas.........(use however many zeros you need).....

=TEXT(A1,"0000000")

Vaya con Dios,
Chuck, CABGx3





"Laury" wrote:

I have a column of numbers with lead zeros, formatted as Numer PESEL. I need
to get these numbers into text format, but keep the lead zeros and I can't
figure out how.

When I double click on the cell, the zeros disappear. If I copy and paste
to a text formatted cell, the zeros disappear. Any suggestions?

You have to use the same number of zeros as characters in the original value
(including the leading zeros).....in this case 11.......

=TEXT(A1,"00000000000")

Vaya con Dios,
Chuck, CABGx3



"Laury" wrote:

Didn't work for me. I put in the formula and 00003014150 from the orig cell
changed to 3014150, I then copied and pasted as value and still the cell
showed 3014150. ???


"CLR" wrote:

Use this formula to convert to TEXT, then copy PasteSpecial Values to get rid
of the formulas.........(use however many zeros you need).....

=TEXT(A1,"0000000")

Vaya con Dios,
Chuck, CABGx3





"Laury" wrote:

I have a column of numbers with lead zeros, formatted as Numer PESEL. I need
to get these numbers into text format, but keep the lead zeros and I can't
figure out how.

When I double click on the cell, the zeros disappear. If I copy and paste
to a text formatted cell, the zeros disappear. Any suggestions?

Thank you so much! I thought you meant to put the # of zeros I needed at the
beginning - it works great and you just saved me a ton of manual work!

"CLR" wrote:

You have to use the same number of zeros as characters in the original value
(including the leading zeros).....in this case 11.......

=TEXT(A1,"00000000000")

Vaya con Dios,
Chuck, CABGx3



"Laury" wrote:

Didn't work for me. I put in the formula and 00003014150 from the orig cell
changed to 3014150, I then copied and pasted as value and still the cell
showed 3014150. ???


"CLR" wrote:

Use this formula to convert to TEXT, then copy PasteSpecial Values to get rid
of the formulas.........(use however many zeros you need).....

=TEXT(A1,"0000000")

Vaya con Dios,
Chuck, CABGx3





"Laury" wrote:

I have a column of numbers with lead zeros, formatted as Numer PESEL. I need
to get these numbers into text format, but keep the lead zeros and I can't
figure out how.

When I double click on the cell, the zeros disappear. If I copy and paste
to a text formatted cell, the zeros disappear. Any suggestions?

Happy to help.........and thanks for the feedback.......

Vaya con Dios,
Chuck, CABGx3



"Laury" wrote:

Thank you so much! I thought you meant to put the # of zeros I needed at the
beginning - it works great and you just saved me a ton of manual work!

"CLR" wrote:

You have to use the same number of zeros as characters in the original value
(including the leading zeros).....in this case 11.......

=TEXT(A1,"00000000000")

Vaya con Dios,
Chuck, CABGx3



"Laury" wrote:

Didn't work for me. I put in the formula and 00003014150 from the orig cell
changed to 3014150, I then copied and pasted as value and still the cell
showed 3014150. ???


"CLR" wrote:

Use this formula to convert to TEXT, then copy PasteSpecial Values to get rid
of the formulas.........(use however many zeros you need).....

=TEXT(A1,"0000000")

Vaya con Dios,
Chuck, CABGx3





"Laury" wrote:

I have a column of numbers with lead zeros, formatted as Numer PESEL. I need
to get these numbers into text format, but keep the lead zeros and I can't
figure out how.

When I double click on the cell, the zeros disappear. If I copy and paste
to a text formatted cell, the zeros disappear. Any suggestions?

Add some more leading zeroes to Chuck's formula.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Laury" wrote in message
...
Didn't work for me. I put in the formula and 00003014150 from the orig
cell
changed to 3014150, I then copied and pasted as value and still the cell
showed 3014150. ???


"CLR" wrote:

Use this formula to convert to TEXT, then copy PasteSpecial Values to
get rid
of the formulas.........(use however many zeros you need).....

=TEXT(A1,"0000000")

Vaya con Dios,
Chuck, CABGx3





"Laury" wrote:

I have a column of numbers with lead zeros, formatted as Numer PESEL.
I need
to get these numbers into text format, but keep the lead zeros and I
can't
figure out how.

When I double click on the cell, the zeros disappear. If I copy and
paste
to a text formatted cell, the zeros disappear. Any suggestions?