Fill a Cell with Repeating Characters

December 29, 2021 - by Bill Jelen

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.

A cool trick in custom number formatting. A format of **0.00 will fill the space to the left of the number with repeating asterisks. The code of asterisk asterisk works like this: The first asterisk says to repeat the next character. The second asterisk is specifying that you want asterisks to be the repeating character. Other examples here show that you can use asterisk plus to repeat plus signs or asterisk X to repeat X. You could even put 0.00 asterisk period to fill the space after the number with periods. The final two cells here show a number in thousands and a number in hundreds. The smaller number gets one extra asterisk. — Figure 243. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Michael Dziedzic on Unsplash