Right with Trailing Spaces

SEdholm

New Member
Joined
Apr 13, 2011
Messages
1
I am working on a program that will eventually automate the creation of text files and put them in a folder. There is a specific length of the rows, 85 (followed by a 4 line counter), and length of each field.

So far most of it has been pretty easy. We have an address (and up to 4 of them) that can be up to, lets say, 70 characters. The issue is that the address starts on line space 65. Since the field can be from 0-70 characters (not a required field) I need a way to parse out anything over the 20th character and if it is less then 70, add trailing 0s. I have been calling fields from other tabs and have something like:

="ASC1 5 "&LEFT(MN_name&REPT(" ",35),35)&LEFT(MN_addr1&REPT(" ",20),20)&"0017"

That part works perfectly. So I have been using a lot of the LEFT(field&REPT(" "#),#). I have tried using &RIGHT(MN_add1&REPT(" ",50),50), but it seems to be just all spaces.

I need my results to be like:
Line 1 ends with "2 WORK SONG RD, " (1 space at end)
Line 2 starts with "NEW DISTRICT " (37 spaces)

What I see is:
Line 1 ends with "2 WORK SONG RD, " (1 space at end)
Line 2 starts with " " (50 spaces)

Any help would be amazing.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,137,155
Messages
5,679,921
Members
419,862
Latest member
Bluewings666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top