Need help concatenating with a specific spacing format

[Jeffrey Green]

let me try to get this right . . . lol I prefer this in a macro, but a formula is fine . .thanks
i have 4 columns; A, B C and D.

All four are usually populated, with A having the highest probability of not being populated (A, B and C are prefix, base, suffix of a part number)

I need to put all four of these columns into 1 column, let's call it E.

1. I need the Column A portion of the output to be 10 characters wide, regardless of the number of characters actually in the cell, with spaces inserted to LEFT of any characters. If A is empty, i need 10 spaces. If the is an G in column A, i need 9 spaces and the G, etc.

2. I also need the same formatting for Column B. with any needed spaces added to the left of any characters in column B.

3. Now I need Column C to 10 characters in length, but this time with any needed spaces to the RIGHT of any characters actually in Column C.

4. Now the rub . . . I need the "right justified" 10 characters from column B combined with the "left justified" 10 characters from Column C.
Thus combining B and C into 1 string with any needed spaces on the "outside" of the string.

5. Then Column D needs to be 8 spaces, with any extra spaces added to the LEFT of any characters actually in column D.

Before:

5C2Z FHFYF24 AB 99999999

After:
5C2Z FHFYF24AB 999999999
6 spaces first 3 spaces first, then 8 spaces after AB then 8 nines

So i need 4 Columns, A,B,C and D, concatenated into one Cell, E, with three sections within Column E.

God, i hope that makes sense . . . . I hate my client..

Thanks

 

[Tetra201]

Try this formula:

=REPT(" ",10-LEN(A1))&A1&REPT(" ",10-LEN(B1))&B1&C1&REPT(" ",10-LEN(C1))&REPT(" ",8-LEN(D1))&D1

 

[Jeffrey Green]

that seems to work!! thanks

 

[Tetra201]

Glad to be of a help.