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
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,893
Messages
5,834,273
Members
430,273
Latest member
Windrunner

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