Home |
Search |
Today's Posts |
#1
|
|||
|
|||
I want to format a number cell without the decimal and without ro.
I need to format the number cell so that it will still show the numbers after
the decimal, but not show the decimal point at all. It must also display leading zeros, which is not a problem. I just need a way to strip the decimal from the number. |
#2
|
|||
|
|||
If if needs to stay a number with the same value, I don't think you can do it.
If you can change the value Just multiple the number by ten to the number of decimal places. If it can be text set up a new column and use =substitute(a1,".',"") "LAM" wrote: I need to format the number cell so that it will still show the numbers after the decimal, but not show the decimal point at all. It must also display leading zeros, which is not a problem. I just need a way to strip the decimal from the number. |
#3
|
|||
|
|||
The column needs to be formatted numeric. This is a file that will be
transmitted to the bank to verify Payables check amounts. It is called an ARP Standard Issue Input Format. If it is "Standard" I wonder why it is so difficult to do? I am creating a .csv file from our Payables system with all of the information needed. It is formatting it for the ARP Standard that is giving me fits. "bj" wrote: If if needs to stay a number with the same value, I don't think you can do it. If you can change the value Just multiple the number by ten to the number of decimal places. If it can be text set up a new column and use =substitute(a1,".',"") "LAM" wrote: I need to format the number cell so that it will still show the numbers after the decimal, but not show the decimal point at all. It must also display leading zeros, which is not a problem. I just need a way to strip the decimal from the number. |
#4
|
|||
|
|||
On Mon, 18 Apr 2005 08:22:01 -0700, LAM wrote:
The column needs to be formatted numeric. This is a file that will be transmitted to the bank to verify Payables check amounts. It is called an ARP Standard Issue Input Format. If it is "Standard" I wonder why it is so difficult to do? I am creating a .csv file from our Payables system with all of the information needed. It is formatting it for the ARP Standard that is giving me fits. I don't know what that format is. If you were in the US, then possibly this format is merely dollars and cents, without the decimal. If that is the case your original numbers should have two decimal precision. Just multiply the numbers by 100. If some of these values are the result of computations, ROUND them to two decimal first. e.g. =100*ROUND(A1,2) How to do this most easily depends on more information than you have posted. --ron |
#5
|
|||
|
|||
I also am not familiar with that format
"Ron Rosenfeld" wrote: On Mon, 18 Apr 2005 08:22:01 -0700, LAM wrote: The column needs to be formatted numeric. This is a file that will be transmitted to the bank to verify Payables check amounts. It is called an ARP Standard Issue Input Format. If it is "Standard" I wonder why it is so difficult to do? I am creating a .csv file from our Payables system with all of the information needed. It is formatting it for the ARP Standard that is giving me fits. I don't know what that format is. If you were in the US, then possibly this format is merely dollars and cents, without the decimal. If that is the case your original numbers should have two decimal precision. Just multiply the numbers by 100. If some of these values are the result of computations, ROUND them to two decimal first. e.g. =100*ROUND(A1,2) How to do this most easily depends on more information than you have posted. --ron |
#6
|
|||
|
|||
For example, my input data file brings in the amount 72208.88. For the
submission file this entry needs to appear 000007220888 The decimal is implied and the amount needs to be right justified and zero filled, with the column width at 12. "bj" wrote: I also am not familiar with that format "Ron Rosenfeld" wrote: On Mon, 18 Apr 2005 08:22:01 -0700, LAM wrote: The column needs to be formatted numeric. This is a file that will be transmitted to the bank to verify Payables check amounts. It is called an ARP Standard Issue Input Format. If it is "Standard" I wonder why it is so difficult to do? I am creating a .csv file from our Payables system with all of the information needed. It is formatting it for the ARP Standard that is giving me fits. I don't know what that format is. If you were in the US, then possibly this format is merely dollars and cents, without the decimal. If that is the case your original numbers should have two decimal precision. Just multiply the numbers by 100. If some of these values are the result of computations, ROUND them to two decimal first. e.g. =100*ROUND(A1,2) How to do this most easily depends on more information than you have posted. --ron |
#7
|
|||
|
|||
On Mon, 18 Apr 2005 11:37:03 -0700, LAM wrote:
For example, my input data file brings in the amount 72208.88. For the submission file this entry needs to appear 000007220888 The decimal is implied and the amount needs to be right justified and zero filled, with the column width at 12. Since you are exporting it as a CSV file with a fixed (12) number of spaces in the column, justification should be irrelevant. To convert your numbers: =TEXT(ROUND(A1*100,2),"000000000000") Then copy/paste special values back over the original numbers. --ron |
#8
|
|||
|
|||
It sounds as though multiplying by 100 and using a <format<cells<custom
type [0000000000000] should give you the output you need "LAM" wrote: For example, my input data file brings in the amount 72208.88. For the submission file this entry needs to appear 000007220888 The decimal is implied and the amount needs to be right justified and zero filled, with the column width at 12. "bj" wrote: I also am not familiar with that format "Ron Rosenfeld" wrote: On Mon, 18 Apr 2005 08:22:01 -0700, LAM wrote: The column needs to be formatted numeric. This is a file that will be transmitted to the bank to verify Payables check amounts. It is called an ARP Standard Issue Input Format. If it is "Standard" I wonder why it is so difficult to do? I am creating a .csv file from our Payables system with all of the information needed. It is formatting it for the ARP Standard that is giving me fits. I don't know what that format is. If you were in the US, then possibly this format is merely dollars and cents, without the decimal. If that is the case your original numbers should have two decimal precision. Just multiply the numbers by 100. If some of these values are the result of computations, ROUND them to two decimal first. e.g. =100*ROUND(A1,2) How to do this most easily depends on more information than you have posted. --ron |
#9
|
|||
|
|||
Thanks, bj
THat works like a charm! "bj" wrote: It sounds as though multiplying by 100 and using a <format<cells<custom type [0000000000000] should give you the output you need "LAM" wrote: For example, my input data file brings in the amount 72208.88. For the submission file this entry needs to appear 000007220888 The decimal is implied and the amount needs to be right justified and zero filled, with the column width at 12. "bj" wrote: I also am not familiar with that format "Ron Rosenfeld" wrote: On Mon, 18 Apr 2005 08:22:01 -0700, LAM wrote: The column needs to be formatted numeric. This is a file that will be transmitted to the bank to verify Payables check amounts. It is called an ARP Standard Issue Input Format. If it is "Standard" I wonder why it is so difficult to do? I am creating a .csv file from our Payables system with all of the information needed. It is formatting it for the ARP Standard that is giving me fits. I don't know what that format is. If you were in the US, then possibly this format is merely dollars and cents, without the decimal. If that is the case your original numbers should have two decimal precision. Just multiply the numbers by 100. If some of these values are the result of computations, ROUND them to two decimal first. e.g. =100*ROUND(A1,2) How to do this most easily depends on more information than you have posted. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
16 digit number wont keep alteration unless format cell to text | Excel Discussion (Misc queries) | |||
Format a cell to display decimal hours. | Excel Discussion (Misc queries) | |||
Format decimal controls do not work | Excel Discussion (Misc queries) | |||
number format keeps dividing by 100 | New Users to Excel | |||
Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions |