Fixed length string of text

  • Thread starter Thread starter MWysack
  • Start date Start date
M

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
 
Assuming you want the Last name to be exactly 9 characters...
=CONCATENATE(A1,B1,C1&IF(LEN(C1)<=9,REPT(" ",9-LEN(C1)),""),D1)
 
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)))
 
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
 
best I think you may get for variable lengths
of names and widths of letters
Click to expand...

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.
 
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
 
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
Click to expand...

This solution just worked fantastically for me - thanks
 
Back
Top