Bulleted Lists in Excel

[xlhelp]

This has also been submitted as FAQ: [Link javascriptpenindex(450,450,'

http://www.tipmaster.com/includes/refinfo.cfm?w=450&h=450')

]FAQ68-7219[/url]

[red]Bullet in a single cell:[/red]

This one is easy. Most people know about this one.

Place your cursor at the beginning of the cell; hold the [blue]ALT[/blue] key and on the numeric keypad only, enter [blue]0149[/blue].

[red]Inserting bullets in multiple cells:[/red]

Using a Formula:

This needs to be done in another column with a formula. If, for example, the text you want bulleted is in column C; then taking cell C2 as our reference we would write a formula in cell D2 –
[blue]=REPLACE(C2,1,LEN(C2),CHAR(149)&C2)[/blue]
and copy down as far as you wish. You now have a bulleted list in Column D. Highlight the bulleted cells in Column D; Copy; select cell C2, and using Paste Special, paste Values. Delete the bulleted list in column D.

If you want a space between the bullet and the text, change the formula to
[blue]=REPLACE(C2,1,LEN(C2),CHAR(149)&” “&C2)[/blue].
Note: There is a space between the two quotation marks.

Using Custom Formatting

Highlight the cell(s) that will have bulleted text. Go to [blue]Format, Cells, Number Tab[/blue]. Under [blue]Categories[/blue], choose [blue]Text[/blue] first and then choose [blue]Custom[/blue]. Place your cursor before the @ sign in the [blue]Type:[/blue] box. Hold the [blue]ALT[/blue] key and on the numeric keypad only, enter [blue]0149[/blue]. (if you want a space between the bullet and the text, type in the space after entering 0149). Click [blue]OK[/blue] to accept the Custom Format.

[red]Inserting multiple bullets in a single cell:[/red]

(Please note:- This method works only if you have less than 1024 characters in the cell. Else, you will get an error “Formula is too long”)

Type your list(s) in the cell(s) the as you need using [blue]ALT/ENTER[/blue] to create each new line in the cell. Once you have typed in all your data, go to [blue]Edit, Replace[/blue]. The Find and Replace dialog box will pop up. Place your cursor in the [blue]Find what:[/blue] box and hold the [blue]ALT key and on the numeric keypad only, enter [blue]0010[/blue]. Let go of the [blue]ALT[/blue] key. Place your cursor in the [blue]Replace with: box and Hold the [blue]ALT[/blue] key and on the numeric keypad only, enter [blue]0149[/blue]. Let go of the [blue]ALT[/blue] key and type in a space if you wish to have a space after the bullet. In the [blue]Replace with:[/blue] box, place your cursor just before the bullet; hold the [blue]ALT[/blue] key and on the numeric keypad only, enter [blue]0010[/blue]. Let go of the [blue]ALT[/blue] key. Click [blue]Replace All[/blue] on the Find and Replace dialog box and your list(s) will be populated with bullets except for the very first line. You can add the bullet to the first line using the method described above for “Bullet in a single cell”.


A man has only two choices: He can be right or he can be happy.

 

[SkipVought]

Why not simply
[tt]
=CHAR(149)&C2
=CHAR(149)&" "&C2
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlhelp]

Yup, You are right. I tend to make things complicated.

Now, I think had some problem with the simple formula; but, can't remember what it was.

A man has only two choices: He can be right or he can be happy.