Fixed length string of text

[MWysack]

Hello,
I am trying to create strings of text by combining cells using CONCATENATE.
I need to have each string of text to be the same length. Below are some
examples of the text strings:

A1= 0000001
B1=123456789
C1= Doe
D1= John

A2=0000002
B2=987654321
C2=Smith
D2=John

In need the lines to look like this:
0000001123456789Doe John
0000002987654321Smith John

This is what they look like when I use CONCATENATE:
0000001123456789DoeJohn
0000002987654321SmithJohn

Is there anyway to get these strings of text to look like this without
manually adding the spaces for each line?

Thanks,
Megan

 

[Per Jessen]

Hi Megan

Look at this:

=A1&B1&C1&LEFT(" ",30-LEN(A1&B1&C1&D1))&D1

Regards,
Per

 

[Gary Brown]

Assuming you want the Last name to be exactly 9 characters...
=CONCATENATE(A1,B1,C1&IF(LEN(C1)<=9,REPT(" ",9-LEN(C1)),""),D1)

 

[Ron@Buy]

MWysack
You have a considerable problem here to give a visual look to the line as
you show it. Consider the width of the letters 'm' & 'i' so a name with a
double 'm' will visually appear wider than a name with 'i's in it.
Anyhow try this as being the best I think you may get for variable lengths
of names and widths of letters:
=REPLACE(A1&B1&C1&D1,LEN(A1&B1&C1)+1,0,REPT(" ",30-LEN(A1&B1&C1)))

 

[Ron@Buy]

Small modification:
=REPLACE(A1&B1&C1&D1,LEN(A1&B1&C1)+1,0,REPT(" ",59-LEN(A1&B1&C1&C1)))
Might give a slightly better look

 

[T. Valko]

best I think you may get for variable lengths

of names and widths of letters

You can get it aligned very nicely if you use a fixed width font like
Courier New. You'd have to define the min number of spaces you want between
the strings.

 

[FSt1]

hi
i did this a tad different.
assuming data in A1 and B1 would be standart length.....
in E1, put this to establish a template.
=A1&B1&C1&" "&D1 with 6 spaces to start. change if needed.
using E1 as a template in E2, enter....
=A2&B2&C2&REPT(" ",LEN(E$1)-LEN(A2&B2&C2&D2))&D2
all cells below E1 ended up with the same number of characters. i tested 10.
and using Biff's suggestion put a nice square edge to it all.

downside. longest name must be in E1 else error will occur further down the
list.
not sure if that is a problem.

regards
FSt1

 

[willgmiller]

Assuming you want the Last name to be exactly 9 characters...
=CONCATENATE(A1,B1,C1&IF(LEN(C1)<=9,REPT(" ",9-LEN(C1)),""),D1)
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

This solution just worked fantastically for me - thanks