REPT Function Lining up Text

RandyKelly19

New Member
Joined
Feb 18, 2011
Messages
6
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,478
I get this far... What next?
Excel Workbook
AB
1Smith, Johnjsmith
2Washington, Georgegwashington
Sheet1
Excel 2007
Cell Formulas
RangeFormula
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

New Member
Joined
Feb 18, 2011
Messages
6
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>
 

RandyKelly19

New Member
Joined
Feb 18, 2011
Messages
6
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,224
Messages
5,594,914
Members
413,952
Latest member
JGer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top