REPT Function Lining up Text

[RandyKelly19]

I thought this would be easy, but is proving to be difficult at least for me.
I am using the CONCATENATE function to string together names and employee ID's. Both fields are variable length and I would like the ID's to follow the names, but all lined up so easily readable.

I thought I could use the REPT function and take the max(len( of name field and subtract the length of each name in the range to provide correct amount of spaces to line up text, but it's not working.

Any suggestions on my math? or better function to use for this ?

example - I cant get it to line up here - but hopefully this question makes since....
Smith, John " "jsmith
Washington, George" "gwashington

 

[jim may]

I get this far... What next?

Excel Workbook
	A	B
1	Smith, John	jsmith
2	Washington, George	gwashington
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=LOWER(MID(A1,FIND(",",A1)+2,1))&LOWER(LEFT(A1,FIND(",",A1)-1))
B2	=LOWER(MID(A2,FIND(",",A2)+2,1))&LOWER(LEFT(A2,FIND(",",A2)-1))

 

[Weaver]

Supposing your name/username is in A2:A3 & B2:B3 and the max length is in C1 (I added 3 for padding)

=MAX(LEN(A2:A3))+3 (array formula) ctrl-shift-enter

in C2

=A2&REPT(" ",C$1-LEN(A2))&B2

Of course you'll need to use a non proportionally spaced font to get the benefit of it.

 

[RandyKelly19]

First, Thank you for speedy response.
Jim - Maybe little confusion - the 2 fields are already seperated I am trying to combine two fields into 1 cell and have them line up nicely.

Weaver - i tried your response and using Arial - which I believe is non proportionally spaced font in excel, but it's still not lining up . Did it work for you?


Here is what I am getting - as you can see the gwashington & rkelly aren't lining up???

<TABLE style="WIDTH: 426pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=567><COLGROUP><COL style="WIDTH: 158pt; mso-width-source: userset; mso-width-alt: 7680" width=210><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 8118" width=222><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 158pt; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17 width=210></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 101pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=135></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 167pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=222 align=right>21</TD></TR>


<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17>Washington, George</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>gwashington</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Washington, George gwashington</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17>Kelly, Randy</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>rkelly</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Kelly, Randy rkelly</TD></TR>


<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" id=td_post_2665311 class=xl65 height=17>Seinfield, Jerry</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>jseinfield</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Seinfield, Jerry jseinfield</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 height=17>Kramer, Kosmo</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>kkramer</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65>Kramer, Kosmo kkramer</TD></TR>
</TBODY></TABLE>

 

[GlennUK]

using Arial - which I believe is non proportionally spaced font in excel

no, you are wrong. Use Courier instead.

 

[RandyKelly19]

Re: REPT Function Lining up Text (RESOLVED)

well, when i changed my font - my response was HUH! Freaken Sweet

I can't believe all of this time my issue was the font - lol

Thank you, Courier works - Is courier the only evenly spaced font in excel - anyone have a link of fonts? - if not I'm sure I can google it

thanks again