all cells with same amount of characters

[Nalani]

I have Column B with different Text in each row. Each cell varies in lenghth of characters. Some with 20, some with 14, some with 8,some with 32, etc.

I know that I can find the longest cell with =Len(B1) and then copy down.

Is there a way to then take that "longest cell" value and add required Spaces to the rest of the cells so they all match in length?

Thanks in advance.

 

[T. Valko]

I have Column B with different Text in each row. Each cell varies in lenghth of characters. Some with 20, some with 14, some with 8,some with 32, etc.

I know that I can find the longest cell with =Len(B1) and then copy down.

Is there a way to then take that "longest cell" value and add required Spaces to the rest of the cells so they all match in length?

Thanks in advance.

If you can use a helper column...

Book1
	A	B	C	D
1	String	Len before	Padded string	Len after
2	this	4	this	17
3	the	3	the	17
4	bad	3	bad	17
5	reallylong	10	reallylong	17
6	stuffy	6	stuffy	17
7	somereallongstuff	17	somereallongstuff	17
8	notreallong	11	notreallong	17
Sheet1

Formula entered in C2 and copied down:

=REPT(" ",MAX(INDEX(LEN(A$2:A$8),0))-LEN(A2))&A2

Then, you can convert the formulas to constants if needed.

Select the range of formulas
Right click>Copy
Right click>Paste Special>Values>OK

 

[tusharm]

Suppose the max. value of the lengths is in B4. Then, use the formula =REPT(" ",$C$4-LEN(B1))&B1 in some cell, say D1 to pad B1 with the necessary leading spaces.

You could skip the intermediate column and use the *array* formula =REPT(" ",MAX(LEN($B$1:$B$3))-LEN(B1))&B1

To enter an array formula complete data entry with the CTRL+SHIFT+ENTER combination rather than just the ENTER or TAB key. If done correctly, *Excel* will show the formula enclosed in curly brackets { and }.

 

[Nalani]

Thanks for the reply from both of you guys.

I think maybe I didn't explain too well. I need to keep the "short cells' still aligned to the left and add the extra spaces to the right of those cells so they all match in character length.

And yes, after the formula is applied, I will have to convert back to Values. Added columns are okay just to do this, can be deleted after.

Valko your solution gives me this after I Pasted Values:

Excel Workbook
	A	B	C	D
1	String	Len before	Padded string	Len after
2	WIRE 36 1X20	13	WIRE 36 1X20	20
3	PAPER ONE PLY 60MIN	19	PAPER ONE PLY 60MIN	20
4	PAPER ONE PLY	13	PAPER ONE PLY	20
5	CAULKING	8	CAULKING	20
6	CAULKING RAIN BUSTER	20	CAULKING RAIN BUSTER	20
Sheet1

tushram your formula gave me this, again after Paste values:

Excel Workbook
	B	C	D
1	WIRE 36 1X20	13	WIRE 36 1X20
2	PAPER ONE PLY 60MIN	19	PAPER ONE PLY 60MIN
3	PAPER ONE PLY	13	PAPER ONE PLY
4	CAULKING RAIN BUSTER	20	CAULKING RAIN BUSTER
Sheet1 (2)

It seems that the added spaces being placed Before the text.

 

[T. Valko]

Thanks for the reply from both of you guys.

I think maybe I didn't explain too well. I need to keep the "short cells' still aligned to the left and add the extra spaces to the right of those cells so they all match in character length.

And yes, after the formula is applied, I will have to convert back to Values. Added columns are okay just to do this, can be deleted after.

Valko your solution gives me this after I Pasted Values:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 164px"><COL style="WIDTH: 88px"><COL style="WIDTH: 184px"><COL style="WIDTH: 75px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 23px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; TEXT-ALIGN: right">String</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; TEXT-ALIGN: right">Len before</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; TEXT-ALIGN: right">Padded string</TD><TD style="FONT-WEIGHT: bold; FONT-FAMILY: Verdana; TEXT-ALIGN: right">Len after</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>WIRE 36 1X20 </TD><TD style="TEXT-ALIGN: right">13</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left"> WIRE 36 1X20 </TD><TD style="TEXT-ALIGN: right">20</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>PAPER ONE PLY 60MIN</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">PAPER ONE PLY 60MIN</TD><TD style="TEXT-ALIGN: right">20</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>PAPER ONE PLY</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left"> PAPER ONE PLY</TD><TD style="TEXT-ALIGN: right">20</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>CAULKING</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left"> CAULKING</TD><TD style="TEXT-ALIGN: right">20</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>CAULKING RAIN BUSTER</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="FONT-FAMILY: Verdana; TEXT-ALIGN: left">CAULKING RAIN BUSTER</TD><TD style="TEXT-ALIGN: right">20</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


tushram your formula gave me this, again after Paste values:
Sheet1 (2)

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 164px"><COL style="WIDTH: 64px"><COL style="WIDTH: 184px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>WIRE 36 1X20 </TD><TD style="TEXT-ALIGN: right">13</TD><TD style="FONT-FAMILY: Verdana"> WIRE 36 1X20 </TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>PAPER ONE PLY 60MIN</TD><TD style="TEXT-ALIGN: right">19</TD><TD style="FONT-FAMILY: Verdana">PAPER ONE PLY 60MIN</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>PAPER ONE PLY</TD><TD style="TEXT-ALIGN: right">13</TD><TD style="FONT-FAMILY: Verdana"> PAPER ONE PLY</TD></TR><TR style="HEIGHT: 22px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>CAULKING RAIN BUSTER</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="FONT-FAMILY: Verdana">CAULKING RAIN BUSTER</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


It seems that the added spaces being placed Before the text.

If you want the spaces added to the right end of the string then a simple tweak is all that's needed:

=A2&REPT(" ",MAX(INDEX(LEN(A$2:A$8),0))-LEN(A2))

 

[Nalani]

Thank you sir, that did the trick.

And I'm not sure if the name is Biff or T. Valko or, if my memory serves me right, you used to have a different Username on this board before changeing it.

Again, my thanks

 

[T. Valko]

Thank you sir, that did the trick.

And I'm not sure if the name is Biff or T. Valko or, if my memory serves me right, you used to have a different Username on this board before changeing it.

Again, my thanks

Thanks for the feedback!

Yes, the name thing is a bit confusing!

My real name is Tony Valko.

My nickname is Biff.

I used to go by the user name of #NAME?.

 

[Nalani]

I used to go by the user name of #NAME?.

That confirms my first "memory recall", just didn't what to post the others that I was thinking.

And now I know the "Biff" part.

Thanks so much, and hope I can get more help when required.

Have a GREAT Night.<!-- / message --><!-- sig -->