# REPT Function Lining up Text

#### RandyKelly19

##### New Member
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
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

=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
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

##### Well-known Member
using Arial - which I believe is non proportionally spaced font in excel
no, you are wrong. Use Courier instead.

#### RandyKelly19

##### New Member
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

Replies
3
Views
118
Replies
5
Views
561
Replies
5
Views
134
Replies
14
Views
512
Replies
0
Views
657

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.

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.

### Which adblocker are you using?

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

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