Excel, combine multiple columns into 1 text record. 2

[diogenes10]

I am trying to create a Social Security Number verification file from payroll masterfile information exported to an Excel spreadsheet.

I have 7 columns of Excel data that have to go into 1 130 column text record. For the text record, each piece of information from the Excel file has to start at a particular position and cannot exceed some number of characters. (For example, the last name has to start at position 16 and cannot be longer than 13 characters.)

I can use the @left function and the & to pull the information out of the Excel columns and insert addition required text and number groups, but that does not give me the control of length or starting position.

What techniques/functions do I need to learn about to do this?

(Basic spreadsheet user and no programming skills.)

Thank you for any suggestions you have for me.
d

 

[SkipVought]

Hi,

You can do this withou any programming by properly formatting the column widths and then File/Save As ... a .prt text file

The way I have done this in the past is to use a FIX PITCH FONT like Courier. Most of the other fonts are VARIABLE PITCH and will not fit properly.

Play around with column widths and how the .prt results turn out.

Skip,

_[red][/red]

 

[yogia]

I have 7 columns of Excel data that have to go into 1 130 column text record. For the text record, each piece of information from the Excel file has to start at a particular position and cannot exceed some number of characters. (For example, the last name has to start at position 16 and cannot be longer than 13 characters.)

Hi diogenes10:

Adding to the suggestion by Skip, for a formula based solution for the part you mentioned in your post, you can use the following formula ...

=REPT(" ",16)&LEFT([b]name[/b],13)&REPT(" ",13-LEN(LEFT([b]name[/b],13)))

and when you know what goes in the first 16 character space, you can make that adjustment, and so with the space after the 13 character space dedicated to the name.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

As a follow-on, change the font to Courier New, change each column width to the character width for that column and Save As .prn.

I checked it - it works!

Skip,

_[red][/red]

 

[diogenes10]

Skip and Yogi,

I am going to give you both stars on this one. I have put this project off for over 2 months because I did not know how to get the final results without literally retyping everything.

Skip,
Initially when I tried this, it did not work for me. Since you said you had tried it and it worked, I kept experimenting and also used notepad to look at the file instead of a wordprocessor.
I was finally able to insert columns for the additional transmission data and blank fields that Social Security administration wanted and then get everything to save properly. I did use Courier instead of Courier New because Courier New had a TT beside it in the font list.
It did not truncate first names when there were no middle names in the following field, so I had to edit that manually.
The SSA instructions talked about a .prn file, but I thought I had to have everything formatted properly first. I did not realize I could format the file by saving it as .prn.
Thanks for your suggestions and research.

Yogi,
I appreciate the time you took to read my problem and give me suggestions.
I did not know about the repeat function.
I think I have heard about the length function in the past and forgotten about it.
I have thought about "string math" as something hard that I probably couldn't do.

I experimented with your suggestions for awhile to try to understand them. I see I can use a combination of LEFT and REPT (with a nested math) to pull out information and get it to a fixed length.
Thank you for teaching me about that. I would have had to create a string formula that included 4 pairs of those, plus instructions about reformatting a date field, inserting 4 additional fields of fixed text or numbers, and 1 additional field of blanks, so I used Skip's solution, just because it was a little easier for me in this case.
I want to remember your concepts because they could be useful on something else that comes along.

d