Text with leading 0 to a number (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Apparently the bank requires them in numeric format – it is for auto deposit for pay cheques to the employees accounts.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Thanks Legare – I will get in touch with the Bank again tomorrow.
Thanks all for your suggestions.

Reply | Quote

What about putting the number and formatting (format – cells – custom) with something like:(no 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

Reply | Quote

Thanks Steve – but all the numbers are a specific length – as required by the bank. Some have leading zeros – others haven’t.

Reply | Quote