Converting negative/positive numbers to a CSV (Comma-delimited .TX
 

If I have numbers in a column in an Excel spreadsheet and I want to import
those numbers into another application (IBM Mainframe), so I have to convert
the numbers to a comma-delimited .TXT file, how do I keep all the numbers
aligned together? My problem is that when I convert it, it misaligns the
negative versus positive numbers. For example:

2.0
-005.2
-006.5
115.9
When I convert these, I would like them to line up as follows in the .TXT
file:

2.0
-005.2
-006.5
115.9

What happens is I see them as this:
002.0
-005.2
-006.5
115.9

My columns are set up as "Custom" with ###.# as their format.

Any suggestions? Is there a way to change the default to where it shows
negatives with a minus sign and positives with a plus (which might line them
up correctly)?

You could use the custom format +###.#;-###.# In Excel the formats are
composed of 3 parts separated by semi-colons: positive;zero;negative. Would
you be better off padding all the numbers with leading spaces so that they
are all the same length?

Something like = REPT(" ",6-LEN(A1))&A1

where A1 contains your number and 6 is the max length of text you want.
--


Simon


"Frustrated Excel user" wrote:

If I have numbers in a column in an Excel spreadsheet and I want to import
those numbers into another application (IBM Mainframe), so I have to convert
the numbers to a comma-delimited .TXT file, how do I keep all the numbers
aligned together? My problem is that when I convert it, it misaligns the
negative versus positive numbers. For example:

2.0
-005.2
-006.5
115.9
When I convert these, I would like them to line up as follows in the .TXT
file:

2.0
-005.2
-006.5
115.9

What happens is I see them as this:
002.0
-005.2
-006.5
115.9

My columns are set up as "Custom" with ###.# as their format.

Any suggestions? Is there a way to change the default to where it shows
negatives with a minus sign and positives with a plus (which might line them
up correctly)?

Another easy way is to install an additional printer (Generic/Text Only) and
associate the printer with a disk file. Printing to this printer would
actually create the file. By setting alignment and print options correctly
you should be able to get the file in any desired format.
--
Gary's Student


"Frustrated Excel user" wrote:

If I have numbers in a column in an Excel spreadsheet and I want to import
those numbers into another application (IBM Mainframe), so I have to convert
the numbers to a comma-delimited .TXT file, how do I keep all the numbers
aligned together? My problem is that when I convert it, it misaligns the
negative versus positive numbers. For example:

2.0
-005.2
-006.5
115.9
When I convert these, I would like them to line up as follows in the .TXT
file:

2.0
-005.2
-006.5
115.9

What happens is I see them as this:
002.0
-005.2
-006.5
115.9

My columns are set up as "Custom" with ###.# as their format.

Any suggestions? Is there a way to change the default to where it shows
negatives with a minus sign and positives with a plus (which might line them
up correctly)?