Need 7 digit number with differnt number of leading zeros

[malycom]

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

 

[PMC1]

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

 

[RonaldoOneNil]

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

 

[Stefi]

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

Regards,
Stefi


„malycom†ezt írta:

 

[malycom]

Many thanks - That worked a treat.

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

Once again, thanks very much.

Malcolm

 

[malycom]

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

I appreciate your assisting though.

Regards

Malcolm

 

[Jacob Skaria]

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

 

[Stefi]

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:

 

[David Biddulph]

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

 

[malycom]

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]

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.