SOLVED

multiple bullet points in an excel cell

%3CLINGO-SUB%20id%3D%22lingo-sub-2701967%22%20slang%3D%22en-US%22%3Emultiple%20bullet%20points%20in%20an%20excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2701967%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20posting%20on%20this%20forum%20because%20I%20ran%20into%20a%20problem.%20I%20am%20currently%20preparing%20an%20excel%20list%20for%20my%20boss%20and%20my%20boss%20told%20me%20that%20in%20an%20excel%20column%20he%20would%20like%20to%20have%20all%20the%20text%20within%20a%20cell%20in%20bullet%20points.%20I%20was%20wondering%20if%20there%20is%20a%20very%20fast%20and%20efficient%20way%20here%20as%20in%20word.%20Otherwise%20I%20always%20have%20to%20do%20%22Alt%2B7%22%20to%20place%20a%20bullet%20point%20%E2%80%A2.%3C%2FP%3E%3CP%3E%3CBR%20%2F%3ENow%20I%20have%20already%20tried%20to%20work%20with%20custom%20formats%2C%20but%20that%20always%20succeeds%20on%201%20cell.%20My%20intention%20is%20to%20put%20multiple%20bullet%20points%20in%20one%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20has%20any%20idea%20you%20would%20be%20a%20hero!%20It%20is%20a%20decent%20list%20where%20bullet%20points%20should%20occur.%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3CBR%20%2F%3EBart%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2701967%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2702203%22%20slang%3D%22en-US%22%3ERe%3A%20multiple%20bullet%20points%20in%20an%20excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2702203%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142140%22%20target%3D%22_blank%22%3E%40Bartballon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUnlike%20Word%2C%20Excel%20is%20not%20a%20real%20text%20processor.%20It%20doesn't%20have%20built-in%20support%20for%20inserting%20bullets.%3C%2FP%3E%0A%3CP%3ESo%20you'll%20have%20to%20insert%20a%20bullet%20in%20front%20of%20each%20line%20in%20a%20cell%20manually.%20You%20could%20insert%20one%20bullet%2C%20and%20copy%20the%20bullet%20and%20the%20space%20after%20it.%20You%20can%20then%20paste%20it%20in%20front%20of%20each%20line.%3C%2FP%3E%0A%3CP%3EOr%20you%20could%20copy%20the%20following%20macro%20into%20a%20module%20in%20the%20Visual%20Basic%20Editor%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20AddBullets()%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Dim%20v()%20As%20String%0A%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20For%20Each%20rng%20In%20Selection%0A%20%20%20%20%20%20%20%20v%20%3D%20Split(rng.Value%2C%20vbLf)%0A%20%20%20%20%20%20%20%20For%20i%20%3D%200%20To%20UBound(v)%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20Left(v(i)%2C%201)%20%26lt%3B%26gt%3B%20Chr(149)%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20v(i)%20%3D%20Chr(149)%20%26amp%3B%20%22%20%22%20%26amp%3B%20v(i)%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20i%0A%20%20%20%20%20%20%20%20rng.Value%20%3D%20Join(v%2C%20vbLf)%0A%20%20%20%20Next%20rng%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EYou%20can%20assign%20the%20macro%20to%20a%20Quick%20Access%20Toolbar%20button%20and%2For%20a%20custom%20keyboard%20shortcut.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20and%20make%20sure%20that%20you%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20use%20the%20macro%20in%20multiple%20workbooks%2C%20save%20it%20in%20your%20personal%20macro%20workbook%20PERSONAL.XLSB.%3C%2FP%3E%0A%3CP%3E(See%20%3CA%20href%3D%22https%3A%2F%2Ftrumpexcel.com%2Fpersonal-macro-workbook%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EExcel%20Personal%20Macro%20Workbook%20%7C%20Save%20%26amp%3B%20Use%20Macros%20in%20All%20Workbooks%3C%2FA%3E%20for%20info%20about%20PERSONAL.XLSB)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2702210%22%20slang%3D%22en-US%22%3ERe%3A%20multiple%20bullet%20points%20in%20an%20excel%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2702210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1142140%22%20target%3D%22_blank%22%3E%40Bartballon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20select%20the%20range%20and%20apply%20custom%20number%20format%20as%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20300px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F306926i1A98E3DB3F8061A9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20apply%20it%20by%20Format%20Painter%20after%20that.%20Or%20create%20the%20style%20with%20such%20formatting.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone,

 

I'm posting on this forum because I ran into a problem. I am currently preparing an excel list for my boss and my boss told me that in an excel column he would like to have all the text within a cell in bullet points. I was wondering if there is a very fast and efficient way here as in word. Otherwise I always have to do "Alt+7" to place a bullet point •.


Now I have already tried to work with custom formats, but that always succeeds on 1 cell. My intention is to put multiple bullet points in one cell.

 

If anyone has any idea you would be a hero! It is a decent list where bullet points should occur.

Thanks in advance!
Bart

 

 

6 Replies
best response confirmed by Bartballon (New Contributor)
Solution

@Bartballon 

Unlike Word, Excel is not a real text processor. It doesn't have built-in support for inserting bullets.

So you'll have to insert a bullet in front of each line in a cell manually. You could insert one bullet, and copy the bullet and the space after it. You can then paste it in front of each line.

Or you could copy the following macro into a module in the Visual Basic Editor:

Sub AddBullets()
    Dim rng As Range
    Dim v() As String
    Dim i As Long
    Application.ScreenUpdating = False
    For Each rng In Selection
        v = Split(rng.Value, vbLf)
        For i = 0 To UBound(v)
            If Left(v(i), 1) <> Chr(149) Then
                v(i) = Chr(149) & " " & v(i)
            End If
        Next i
        rng.Value = Join(v, vbLf)
    Next rng
    Application.ScreenUpdating = True
End Sub

You can assign the macro to a Quick Access Toolbar button and/or a custom keyboard shortcut.

Save the workbook as a macro-enabled workbook and make sure that you allow macros when you open it.

If you want to use the macro in multiple workbooks, save it in your personal macro workbook PERSONAL.XLSB.

(See Excel Personal Macro Workbook | Save & Use Macros in All Workbooks for info about PERSONAL.XLSB)

@Bartballon 

You may select the range and apply custom number format as

image.png

and apply it by Format Painter after that. Or create the style with such formatting.

 

@Sergei Baklan 

Hi Sergei,

 

That custom format will only display a bullet in the first line of a cell. Bart wants to display a bullet in front of each line in a multi-line cell.

@Hans Vogelaar , sorry, I missed that. Thank you for the comment.

O M G, this is exactly what i'm looking for! You're amazing! !!
Thx for the response!