.xlsx to unicode .txt conversion: how do I get the columns straight?

My company works with an outsource group that sends letters for us. We have no control over their business practices and software.

They're insisting that they can only take .txt (unicode version) files.

We're sending them mailings so we have Name, address, address line 2, dates, and a few other pieces of information.

In Excel as a .xlsx file I naturally have it lined up in nice neat rows. When I save it into a .txt file, the nice neat rows are no longer nice and neat. It's causing problems where the date field is getting messed up. Dates of say 1/19/2020 are coming through as "11/1/91".

I need to figure out what's wrong with the dates, but to do that I need to get the columns to convert over and be straight.

How do I get the columns to be lined up in a .txt file I'm converting from a .xlsx file? I want them lined up by the left side of the cell so that (for example) the first letter of everyone's first name is lined up on the left side of the column.

As always, your assistance is greatly appreciated.

Originally Posted by macropod

The following code exports all worksheets in a workbook to fixed-width files. The code is commented for use in either of two modes, to determine the columns widths based on the:
• text width in Row 1; or
• value in Row 1
in the worksheet being exported. The second version also allows for left/right alignments to be set on the basis on a value in row 2.

Please Login or Register  to view this content.

Do note, though, that even with this code, your columns will only look straight if you view the output with a fixed-width font (e.g. Courier New).

Thank you for the thoughtful reply. I am hoping there is a way to do it without a macro/code. Is that possible or no?

Try Save As a .PRN file and see if it covers your requirements.