Concatenating Strings of texts with set limits to # of characters, and need spaces if characters do not reach limit

[Fraser120]

i have strings of text that have limits to how many characters they can be. they also each have a specific location in the entire string that they must begin at.

I need a formula that will concatenate B+C+D+E+F+G+H+I+J. You will see that not all the values in each column are the same length, if they are not I need to add spaces to the end so that the next string of characters when combined, starts at the specific location for that column.

in rows 18,19, and 20 I have manually entered what I require the formula to return for the 3 examples I provided. the red characters correspond with the first character index ( 0=first character, 9=eleventh, T=twenty forth, 4=forty second, A=fifty forth, and so on)

I have set up data validation for the cells, what I need help with is a concatenate formula that will include the necessary amount of spaces following the value if it doesn't exceed the allowed length.

 

[Fluff]

Hi & welcome to MrExcel.
You can do that like

=B4&REPT(" ",10-LEN(B4))&C4&REPT(" ",13-LEN(C4))&D4&REPT(" ",18-LEN(D4))

 

[johnnyL]

-LEN(D4) ?

 

[Fluff]

Good spot, now corrected.

 

[Anthony47]

Didn't understand if the question is now fixed...
I should suggest, in B18:

=LET(DataR,B4:J4,LenR,$B$1:$J$1,Rdata,DataR&REPT(" ",MAX(0,LenR-LEN(DataR))),TEXTJOIN("",TRUE,Rdata))

 

[Fluff]

Slight mod to Anthony47's idea

=LET(DataR,B4:J4,LenR,$B$1:$J$1,Rdata,DataR&REPT(" ",LenR-LEN(DataR)),TEXTJOIN("",TRUE,Rdata))

and if you don't have the allowed length in row 1

=LET(DataR,B4:J4,LenR,{10,13,18,10,24,10,10,3,18},Rdata,DataR&REPT(" ",LenR-LEN(DataR)),TEXTJOIN("",TRUE,Rdata))

 

[Anthony47]

I added MAX(0, etc etc) to cope with possible strings whose lenght be longer then the "allowed lenght"; I didn't tested its effect, but @Fluff did and removed that portion, but now the formula could return the #VALUE! error in that situation.
To avoid that risk, my penultimate proposal is:

=LET(DataR,B4:J4,LenR,$B$1:$J$1,Rdata,DataR&REPT(" ",(LenR)),TEXTJOIN("",TRUE,LEFT(Rdata,LenR)))