Need a line break that exports into space delimited

Hello. I am going to try to describe what I need as best I can- caveat- I am not a programmer or even very good at Excel. I am working on a project to export NACHA formatted files. NACHA format is what we upload to the bank to initiate ACH payments. Each record (or line) is 94 characters. I have no problem getting the record formatted to the proper 94 characters. The problem is I need to find a way to put a line break (carriage return?) into a column so that when I export as a .PRN (formatted text, space delimited) A single row in my spreadsheet will show as two lines of text. Does that make sense? I have tried inserting a column where I need the break and putting =Char(10) in the column. All this does at export is give me one blank space. In case I have not explained correctly- here is another way of explaining. My worksheet has columns A-P. It is set up space delimited. When I save as a .prn (or text) I get:
A B C D E F G H I J K L M N O P
A B C D E F G H I J K L M N O P
A B C D E F G H I J K L M N O P


What I need is:
A B C D E F G H I J K
L M N O P
A B C D E F G H I J K
L M N O P

I have also tried adding in a column and making it 146 wide (so I hit the space delimited max line length of 240) and here is what I get:
A B C D E F G H I J K
A B C D E F G H I J K
A B C D E F G H I J K
L M N O P
L M N O P
L M N O P

I hope someone out there can help me with this!

Thanks!!

I think you need to concatenate those columns into one column with a CHAR(10) at the end of 94 characters, maybe in another sheet, and then you just export the one column to the .prn file.

To give any more detailed advice I would need to see a sample of the Excel workbook.

Hope this helps.

Pete

Hello Pete (and anyone else who wants to take a stab at this),

Thank you for your reply. Here is the workbook. The first spreadsheet is the template into which the tax associates enter the payment details. The second tab is my WIP to automate the ACH entry. In order to upload to the bank we have to have the file formatted according to NACHA guidelines. Each state needs two lines in the payment file. The first line ends after column K. Since it must be space delimited I am unable to just put the second line information on a second row because the column widths are different.
Note: Q-AD are usually hidden so that they don't export. I have also attached two .txt files. ACH Help.txt is how it currently looks at export. ACH Help ideal.txt is what I am hoping to accomplish.

Thanks!

See if Sheet1 in the attached file gives you the correct output.

Hope this helps.

Pete

Hi Pete,

It looks great on the spread sheet but when I save it as a .prn or a .txt it doesn't hold the formatting. See attached. Did you get to see the eclipse today?

Thanks,

Amber

384273d1426817893-need-a-line-break-that-exports-into-space-delimited-ef703_eft-help.txt

Hi Amber,

Originally Posted by Xaita

Did you get to see the eclipse today?

No, it was cloudy here this morning, so it just went even more gloomy when the eclipse occurred.

I've modified the file by removing the CHAR(10) from the formula in column A and putting a new formula in column B which splits the record in half on alternate rows. Hide column A and see if this gives you the text format you require.

Obviously for the real file you will need to extend the formulae further down.

Hope this helps.

Pete

Wow that is genius! It is beautiful :-) Thank you soooo soooooo much for making my life that much easier!

Amber

Well, that's good to hear - thanks for the rep and for marking the thread as SOLVED.

It did occur to me that column A on Sheet 1 could actually be on the other sheet to make it even easier for you - do you want me to have a go at that?

Pete

Well, I went ahead anyway!

In the attached file I've combined the concatenation formula from Sheet1 with the various formulae you had on the Prenote_test sheet, and that means I have been able to get rid of a number of columns that you had before. You could also improve some of the formulae in the remaining columns by using VLOOKUP or INDEX/MATCH, but I've left them as they are.

Sheet1 (which I've now called Export) has only one column for the export, so you just need to ensure that the formula is copied down far enough to cover all the data (to row 82 in this case).

Hope this helps.

Pete

LOL Thanks, Pete! Now it is super fancy :-)

-Amber