Excel; Auto Repeating Character in a cell 4

[mikemck7]

How do I repeat a character to fill a cell such that there is no overflow into the adjacent cell regardless of the width of the cell?

 

[SkipVought]

Hi,


It depends on the FONT. If you are using a proportional font, (most of them are) you get more i's than w's.

It would be most easily done using a FIXED pitch font like Courier.
[tt]
=rept("I",8)
[/tt]
If you entered the column width in row 1, then the formula in column A would be...
[tt]
=rept("I",A$1)
[/tt]
However, you have to know the column width. There is no spreadsheet function that returns the column width. You will have to proved that value.

If you want to try a VBA solution, post in Forum707.

Skip,

_[red][/red]

 

[yogia]

However, you have to know the column width. There is no spreadsheet function that returns the column width. You will have to proved that value.

Hi Skip:

How about using =CELL("WIDTH") to determine the width of column!

Would you happen to know what is the width of a character in a non-proportional font like COURIER? I have a home grown number of 1.13 units in which the EXCEL Column width is meaured.

I might have a little play with it and see what I get.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Hi mike:

A       B                       C               D 
-----------------------------------------------------------
	    =CELL("width",B2)	  =INT(B2/D2)	width of 1 CHAR in Courier
	    25	                 22	         1.13
		=LEN(B4)	
a	aaaaaaaaaaaaaaaaaaaaaa	22	
I	IIIIIIIIIIIIIIIIIIIIII	22	
w	[URL unfurl="true"]wwwwwwwwwwwwwwwwwwwwww[/URL]	22

I know it is a little confusing ... but does it make sense to you mike?


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[SkipVought]

yogi,

COMPLETELY forgot about the CELL function.

I failed to mention that for this to work, the Fixed Pitch font size should be 10.

Skip,

_[red][/red]

 

[mikemck7]

Yogi,
Yes I understand what you're demonstrating but unfortunately I can't use it since I need something that works with proportional fonts.

I have this vague but nagging recollection that ages ago, there was a way to do this by placing a special character into the cell followed by the character to be repeated. Something like ">A" where "A" is the character to be repeated. It might have been an old version of Excel, or maybe even Lotus 123. Yes I'm that old, which might explain the confusion.

Oh well, thanks anyway for your thoughts.
Mike

 

[SkipVought]

What is the purpose of doing this?

Skip,

_[red][/red]

 

[1726]

Try formatting the cells with the horizontal text alignment of Fill.

This repeats the contents of the affected cells to fill their full width.

 

[SkipVought]

1726,

I never knew that about the Fill format. Have another STAR.

However, it still seems to work the best with a Fixed Pitch Font, like Courier as opposed to Arial.

Skip,

_[red][/red]

 

[1726]

Skip - Thanks for the Star. Some of us still remember the days of text only spreadsheets when this approach was used to create horizontal lines.

mikemck7 - you are quite right about a method of typing a repeating label. Using backslash(\) as a prefix creates a repeating label if the Transition Navigation Keys option is switched on. This was commonly used in early versions of Lotus 1-2-3

 

[wec43wec]

This is very interesting !

Question - is there a way to view or determine the various formats that are assigned to any cell in excel which includes conditional formatting?

 

[mikemck7]

Thanks 1726. This is what I was searching for. You get another well deserved star.

I knew there had to be a "fill" factor but I didn't think to look beyond the fill button on the formatting toolbar.

Skip: I'm using this feature as an alternative to filling certain cells with color for visual effect. I'm building a huge table as part of a data modeling project. The effect gets lost as soon as the document is printed, copied or faxed in black and white.

Mike

 

[yogia]

Hi 1726:

Good Call! ... for FORMAT|Cells|Horizontal|Alignment >Fill
as well as use of the \ character with Lotus Transition_navigation keys.

Thanks.


Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[yogia]

Question - is there a way to view or determine the various formats that are assigned to any cell in excel which includes conditional formatting?

Hi wec43wec:

You may want to look at ...

=CELL("FORMAT",A1)



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[xlhelp]

You can also fill a cell with a desired character in addtion to the cell value. For example, if you custom format cells as

#,#00.00*-

it will leave dashes after the last digit.

123.67------


Member- AAAA Association Against Acronym Abusers