I have an xls spreadsheet with 20 columns of data that I need to convert to a
csv file to transmit to our customer on a regular basis. Everything is
working except for one column where they tell me I must have 17 digits. In
some cases I have no data for those cells and I am supposed to include 17
blanks between the commas in the final csv file where I have no data. In
other cases I have 11 digits and will need to add 6 spaces to end up with 17.

Is there a way to set up my Excel file so that those spaces end up between
the columns in my final csv file?

I bet that they want each field to be a certain width--not just that one with
the 17 digits.

If that's true, then I'd create a separate column (the 21st) and use a formula
that would combine what I need:

For instance, if column A had to be 20 characters left justified, I could use:
=left(a1&rept(" ",20),20)

And then just build a big old formula that pieces these together:

=LEFT(A1&REPT(" ",20),20)&","&LEFT(B1&REPT(" ",17),17)&","&TEXT(C1,"mm/dd/yyyy")

Then drag that formula down the column (once you get it perfect).

Then you can just copy that column into Notepad and save the file from there.

ps. Usually when these kinds of files are created (fixed width fields), there's
no need for the commas separating each field.

You may want to ask for a clarification before you go too far with this.

pps. And if you do have 17 digit numbers in a field, make sure you enter them
as text.

You can preformat the field as Text
or
use a leading apostrophe when you do the data entry: '12341234123412341

And if you open this text file in excel, you may find that the 16th and 17th
digits got changed to 0's.

Check your work in NotePad.





Julie wrote:

I have an xls spreadsheet with 20 columns of data that I need to convert to a
csv file to transmit to our customer on a regular basis. Everything is
working except for one column where they tell me I must have 17 digits. In
some cases I have no data for those cells and I am supposed to include 17
blanks between the commas in the final csv file where I have no data. In
other cases I have 11 digits and will need to add 6 spaces to end up with 17.

Is there a way to set up my Excel file so that those spaces end up between
the columns in my final csv file?

--

Dave Peterson

Thank-you Dave - I really appreciate the fast and thorough response.

I followed what you said until you got to copying the column of equations
into notepad. Are you saying to open the csv into notepad first?
1. If so, where in the notepad file do you paste the column you copied from
the csv file?
OR
2. The original xls file I'm using is doing lookups with if/then statements
from a larger xls file. Could these if/then statements handle adding the
spaces so that the data is correct before it ever gets converted to a csv?

--
Julie


"Dave Peterson" wrote:

I bet that they want each field to be a certain width--not just that one with
the 17 digits.

If that's true, then I'd create a separate column (the 21st) and use a formula
that would combine what I need:

For instance, if column A had to be 20 characters left justified, I could use:
=left(a1&rept(" ",20),20)

And then just build a big old formula that pieces these together:

=LEFT(A1&REPT(" ",20),20)&","&LEFT(B1&REPT(" ",17),17)&","&TEXT(C1,"mm/dd/yyyy")

Then drag that formula down the column (once you get it perfect).

Then you can just copy that column into Notepad and save the file from there.

ps. Usually when these kinds of files are created (fixed width fields), there's
no need for the commas separating each field.

You may want to ask for a clarification before you go too far with this.

pps. And if you do have 17 digit numbers in a field, make sure you enter them
as text.

You can preformat the field as Text
or
use a leading apostrophe when you do the data entry: '12341234123412341

And if you open this text file in excel, you may find that the 16th and 17th
digits got changed to 0's.

Check your work in NotePad.





Julie wrote:

I have an xls spreadsheet with 20 columns of data that I need to convert to a
csv file to transmit to our customer on a regular basis. Everything is
working except for one column where they tell me I must have 17 digits. In
some cases I have no data for those cells and I am supposed to include 17
blanks between the commas in the final csv file where I have no data. In
other cases I have 11 digits and will need to add 6 spaces to end up with 17.

Is there a way to set up my Excel file so that those spaces end up between
the columns in my final csv file?

--

Dave Peterson

Select the column with the formulas in them.
Copy that selected range

Open NotePad
You can traverse the windows start button|programs|accessories and choose
Notepad

I like this:
Hit and hold the Flying Windows key (between the control and alt key) and hit r
(flying window-r) to show the Run dialog.

Type:
Notepad
and hit enter

Paste (ctrl-v) that copied stuff into this newly opened Notepad file.
Inside Notepad, File|SaveAs
and save it where you want.

Since there's nothing in Notepad, it'll be the only thing there when you're
done.

============
Personally, I wouldn't fiddle with the existing formulas. I'd just use that
extra column (and never delete that column--but hiding it if it's irritating is
ok!)

But you may be able to use something like:

=left(vlookup(a1,sheet2!a:e,3,false)&rept(" ",20),20)

as a replacement formula.

But if you have error checking in that formula, the formula gets more unwieldy.

And my personal rule is to use easy formulas in helper columns. It makes it
easier to update, removes (er, reduces) the chance that I'd make a mistake, and
makes my brain hurt less!

Julie wrote:

Thank-you Dave - I really appreciate the fast and thorough response.

I followed what you said until you got to copying the column of equations
into notepad. Are you saying to open the csv into notepad first?
1. If so, where in the notepad file do you paste the column you copied from
the csv file?
OR
2. The original xls file I'm using is doing lookups with if/then statements
from a larger xls file. Could these if/then statements handle adding the
spaces so that the data is correct before it ever gets converted to a csv?

--
Julie

"Dave Peterson" wrote:

I bet that they want each field to be a certain width--not just that one with
the 17 digits.

If that's true, then I'd create a separate column (the 21st) and use a formula
that would combine what I need:

For instance, if column A had to be 20 characters left justified, I could use:
=left(a1&rept(" ",20),20)

And then just build a big old formula that pieces these together:

=LEFT(A1&REPT(" ",20),20)&","&LEFT(B1&REPT(" ",17),17)&","&TEXT(C1,"mm/dd/yyyy")

Then drag that formula down the column (once you get it perfect).

Then you can just copy that column into Notepad and save the file from there.

ps. Usually when these kinds of files are created (fixed width fields), there's
no need for the commas separating each field.

You may want to ask for a clarification before you go too far with this.

pps. And if you do have 17 digit numbers in a field, make sure you enter them
as text.

You can preformat the field as Text
or
use a leading apostrophe when you do the data entry: '12341234123412341

And if you open this text file in excel, you may find that the 16th and 17th
digits got changed to 0's.

Check your work in NotePad.





Julie wrote:

I have an xls spreadsheet with 20 columns of data that I need to convert to a
csv file to transmit to our customer on a regular basis. Everything is
working except for one column where they tell me I must have 17 digits. In
some cases I have no data for those cells and I am supposed to include 17
blanks between the commas in the final csv file where I have no data. In
other cases I have 11 digits and will need to add 6 spaces to end up with 17.

Is there a way to set up my Excel file so that those spaces end up between
the columns in my final csv file?

--

Dave Peterson


--

Dave Peterson