Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I saved
the changes I made in the spreadsheet and all the leading zeros were lost.

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits and 1
leading zero for the numbers with 6 digits. I desperately need this sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

On Jul 30, 11:43*am, malycom
wrote:
Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. *The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as *0003128 right
through to 0999999 and again, everything in between. *Unfortunately, I saved
the changes I made in the spreadsheet and all the leading zeros were lost..

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits and 1
leading zero for the numbers with 6 digits. *I desperately need this sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

Hi Malcolm,

This basic but it will work. Format a column as text and enter this
formula then copy down the required number of rows

=IF(LEN(A1)=4,"000"&A1,IF(LEN(A1)=5,"00"&A1,IF(LEN (A1)
=6,"0"&A1,A1)))

(Where the numbers are in column A)

Hope this helps

Paul

Highlight all the cells and then select Format - Cells
Custom and enter into the type box where it says general, 0000000

"malycom" wrote:

Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I saved
the changes I made in the spreadsheet and all the leading zeros were lost.

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits and 1
leading zero for the numbers with 6 digits. I desperately need this sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

=TEXT(A1,"0000000")
creates the required string in a helper column, then format the original
column like text and CopyPasteSpecial/Values the helper column to the
original column and delete the helper column!

Regards,
Stefi


€žmalycom€� ezt Ã*rta:

Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I saved
the changes I made in the spreadsheet and all the leading zeros were lost.

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits and 1
leading zero for the numbers with 6 digits. I desperately need this sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

Many thanks - That worked a treat.

The crazy thing is I was convinced I tried that several times.

Once again, thanks very much.

Malcolm

"RonaldoOneNil" wrote:

Highlight all the cells and then select Format - Cells
Custom and enter into the type box where it says general, 0000000

"malycom" wrote:

Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I saved
the changes I made in the spreadsheet and all the leading zeros were lost.

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits and 1
leading zero for the numbers with 6 digits. I desperately need this sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

Hi, thanks for your input but I tried Ronaldo's suggestion first and it worked.

I appreciate your assisting though.

Regards

Malcolm

"PMC1" wrote:

On Jul 30, 11:43 am, malycom
wrote:
Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I saved
the changes I made in the spreadsheet and all the leading zeros were lost..

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits and 1
leading zero for the numbers with 6 digits. I desperately need this sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

Hi Malcolm,

This basic but it will work. Format a column as text and enter this
formula then copy down the required number of rows

=IF(LEN(A1)=4,"000"&A1,IF(LEN(A1)=5,"00"&A1,IF(LEN (A1)
=6,"0"&A1,A1)))

(Where the numbers are in column A)

Hope this helps

Paul

If you are looking to convert the below data in Col A to that in colB; try
the below formula in Cell B1 and copy down

=TEXT(LEFT(A1,FIND("-",A1)-1)+0,"0000000") & " - " &
TEXT(MID(A1,FIND("-",A1)+1,10)+0,"0000000")

ColA COLB
3128 - 9999 0003128 - 0009999
10000 - 99999 0010000 - 0099999
100000 - 999999 0100000 - 0999999


If this post helps click Yes
---------------
Jacob Skaria


"malycom" wrote:

Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I saved
the changes I made in the spreadsheet and all the leading zeros were lost.

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits and 1
leading zero for the numbers with 6 digits. I desperately need this sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

It isn't sure that your solution meets the OP's requirement! It shall display
the required format, but cell value remains a number and won't match a 7
digit string identifier!

Stefi


€žRonaldoOneNil€� ezt Ã*rta:

Highlight all the cells and then select Format - Cells
Custom and enter into the type box where it says general, 0000000

"malycom" wrote:

Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I saved
the changes I made in the spreadsheet and all the leading zeros were lost.

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits and 1
leading zero for the numbers with 6 digits. I desperately need this sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

Format as 0000000 if you want it still to be a number.
Use the formula =TEXT(A1,"0000000") if you want text.
--
David Biddulph

"malycom" wrote in message
...
Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I
saved
the changes I made in the spreadsheet and all the leading zeros were lost.

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits
and 1
leading zero for the numbers with 6 digits. I desperately need this
sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

Thanks everyone for your input. I only opened the file in Excel but it is
not saved as an excel spreadsheet. It is saved as an .req file which is a
kind text file used by a program we use to run daily downloads.

The cutom filed 0000000 works fine for my needs. It was a panic situation
as opening originally in Excel deleted all leading zeros which I hadn't
noticed when saving.

However, everything is sorted now.

Thanks to you all

Malcolm

"David Biddulph" wrote:

Format as 0000000 if you want it still to be a number.
Use the formula =TEXT(A1,"0000000") if you want text.
--
David Biddulph

"malycom" wrote in message
...
Hi

I have a single column of data of ascending numbers which was in an .req
file format (text) which I opened in Excel. The lowest number is 3128 and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128 right
through to 0999999 and again, everything in between. Unfortunately, I
saved
the changes I made in the spreadsheet and all the leading zeros were lost.

I can not for the life of me find anyway of getting 3 leading zeros to all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits
and 1
leading zero for the numbers with 6 digits. I desperately need this
sorted
as these numbers are unique identifiers for a download that occurs daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm

If you use Excel to read in a text file and you want Excel to treat the
input as text, you need to tell Excel (in the text import wizard) that the
data in the relevant column as text, not as general or number.
--
David Biddulph

"malycom" wrote in message
...
Thanks everyone for your input. I only opened the file in Excel but it is
not saved as an excel spreadsheet. It is saved as an .req file which is a
kind text file used by a program we use to run daily downloads.

The cutom filed 0000000 works fine for my needs. It was a panic situation
as opening originally in Excel deleted all leading zeros which I hadn't
noticed when saving.

However, everything is sorted now.

Thanks to you all

Malcolm

"David Biddulph" wrote:

Format as 0000000 if you want it still to be a number.
Use the formula =TEXT(A1,"0000000") if you want text.
--
David Biddulph

"malycom" wrote in message
...
Hi

I have a single column of data of ascending numbers which was in an
.req
file format (text) which I opened in Excel. The lowest number is 3128
and
the largest number is 999999 with all numbers in between.

These numbers were all 7 digits with leading zeros such as 0003128
right
through to 0999999 and again, everything in between. Unfortunately, I
saved
the changes I made in the spreadsheet and all the leading zeros were
lost.

I can not for the life of me find anyway of getting 3 leading zeros to
all
the numbers with 4 digits, 2 leading zero for the numbers with 5 digits
and 1
leading zero for the numbers with 6 digits. I desperately need this
sorted
as these numbers are unique identifiers for a download that occurs
daily.

0003128 - 0009999
0010000 - 0099999
0100000 - 0999999

Is there anybody who can help me to convert these numbers back to their
original format.

Thanks in advance

Malcolm