Results 1 to 10 of 10

20030424, 23:03 #1
 Join Date
 Sep 2002
 Location
 Edmonton, Alberta
 Posts
 92
 Thanks
 0
 Thanked 0 Times in 0 Posts
Text with leading 0 to a number (Excel 2000)
I have a list of bank numbers exported from Quickbooks into Excel. The numbers have changed to text. The text numbers are not a specific length  up to 11 characters and some with leading zeros  up to 3 zeros. How do I change the text to a number and keep the zeros, as the bank needs this format?
Hope someone can help.

20030424, 23:12 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Text with leading 0 to a number (Excel 2000)
If I understand you correctly, the bank numbers are of varying length including the leading zeros. In that case, it is not possible to convert them to numeric while keeping them displayed as they are now (I think).
If you need the numeric value, you can add a column (which may be hidden) with formulas like =VALUE(A1) if A1 is one of the text numbers.

20030424, 23:54 #3
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Text with leading 0 to a number (Excel 2000)
What about putting the number and formatting (format  cells  custom) with something likeno quotes)
"00000000000"
This will always display an eleven digit number and will add zeroes in the front to fill in to any numbers less than 11 digits
Steve

20030425, 00:00 #4
 Join Date
 Sep 2002
 Location
 Edmonton, Alberta
 Posts
 92
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Text with leading 0 to a number (Excel 2000)
Yes, the text is a varying length including the leading zeros. So I guess I can't convert them and keep the correct number of leading zeros. The only thing I thought might be possible is to somehow count the # of characters in each text string then add the correct # of zeros when it is converted. But I don't know how to do something like that in the way of a formula.
Thanks for you help.

20030425, 00:05 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Text with leading 0 to a number (Excel 2000)
Is there a specific reason why you want to convert the bank numbers to numeric? If you have a varying length with a varying number of leading zeros, the bank numbers are essentially text.

20030425, 00:11 #6
 Join Date
 Sep 2002
 Location
 Edmonton, Alberta
 Posts
 92
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Text with leading 0 to a number (Excel 2000)
Apparently the bank requires them in numeric format  it is for auto deposit for pay cheques to the employees accounts.

20030425, 00:13 #7
 Join Date
 Sep 2002
 Location
 Edmonton, Alberta
 Posts
 92
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Text with leading 0 to a number (Excel 2000)
Thanks Steve  but all the numbers are a specific length  as required by the bank. Some have leading zeros  others haven't.

20030425, 00:22 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Text with leading 0 to a number (Excel 2000)
But if they NEED NUMBERS, they should NOT need leading zeroes which are essentially NON numeric.
The options seem to be:
1) what Hans suggested: 2 columns text for display and a second for the VALUE
2) Put EXTRA leading zeroes making ALL the numbers 11 digits (my suggestion)
3) MANUALLY or via a macro to change the displayed format for every single number to the proper number of leading zeroes. You could create essentially 11 different custom formats of 1 to 11 zeroes for the number of "required digits".
Steve

20030425, 00:51 #9
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Text with leading 0 to a number (Excel 2000)
Are you sending them the .xls file, or a txt or csv file created from the workbook? If you are sending them the .xls file, then the bank should be able to tell you exactly what they want and how to do it. They may just mean that they want a text cell with only numbers in it as text with the leading text zeros. Converting the text to numeric is no problem. If the bank is processing this with a program, then it should not make any difference if the leading zeros are or are not dislayed. If they are processing it manually with people, then it should not make any difference if the cell is text or numeric.
The only way to convert the text to numeric and display the values with the leading zeros would be to use a different custom format on every cell.Legare Coleman

20030425, 01:57 #10
 Join Date
 Sep 2002
 Location
 Edmonton, Alberta
 Posts
 92
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Text with leading 0 to a number (Excel 2000)
Thanks Legare  I will get in touch with the Bank again tomorrow.
Thanks all for your suggestions.