This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Fill a Cell with Repeating Characters
Problem: I need to fill a cell with asterisks before the number. If the cell gets wider, I want more asterisks to appear.
Strategy: Use a custom number format.
Select the cells and press Ctrl+1 (Ctrl and one). Select the Number tab. Choose Number or Currency or whatever style you want for your numbers. Then, in the Category list, choose Custom. You will now be able to edit the custom number format code.
To fill a cell with a character, you enter an asterisk and then that character.
For example, to precede a number with asterisks, you would use **0.00. To precede the number with plus signs, use *+0.00. You can have a number and then fill to the right with a character. Use 0*. to fill with periods.
Figure 257 Custom number formats fill the cell.
Additional Details: Lotus 1-2-3 used to support using \* in a cell to fill the cell with asterisks. Excel will still support this, but you have to go to File, Options, Advanced, Transition, and choose Transition Navigation Keys. It seems very unlikely that everyone in your department will want to use these settings, so this method is not as reliable as using the custom number format.