how to format cells so they contain bullets

[chiaramonte.michael]

Experts,
I need to reformat my cell content to display with bullets. For
example:
Sentence one [line break]
Sentence two Sentence two Sentence two Sentence two Sentence two
Sentence two Sentence two Sentence two Sentence two Sentence two
Sentence two Sentence two Sentence two [line break]
[line break]
Sentence three

Macro (or simple reformatting?) should run and replace text so that:
- Sentence one [line break]
- Sentence two Sentence two Sentence two Sentence two Sentence two
Sentence two Sentence two Sentence two Sentence two Sentence two
Sentence two Sentence two Sentence two [line break]
[line break]
- Sentence three

I created a macro that replaces line breaks so that a bullet appears
on the next line but that doesn't really cut it because the first line
is missing the bullet, and any extra lines get a bullet that doesn't
belong.

Sub insertbullets()
For Each c In ActiveCell.CurrentRegion.Cells
c.Value = Application.WorksheetFunction.Substitute(c, Chr(10), Chr(10)
& "-")
Next
End Sub

Any ideas? We are copying over a ton of content from PowerPoint and
need to bring the bullet formatting over with it.

 

[JLatham]

Try this in place of the macro you have.

Sub NewInsertBullets()
'to use: first select all cells you want
'to create bullets in and then run this
'macro.
'Run it on a copy of the real thing -
'it is somewhat destructive in that
'it adds characters to the original
'cell contents, at the very least,
'a "* " at the beginning.
'
Dim anyCell As Object
Dim rawText As String
Dim LC As Integer
'change BulletChar to character you want
'for a bullet; i.e., "- "
Const BulletChar = "* "
'speed things up a little
Application.ScreenUpdating = False
For Each anyCell In Selection
rawText = "" ' clear previous results
If Not IsEmpty(anyCell.Value) Then
rawText = anyCell.Value
If Len(rawText) > 1 Then
'add bullet to 1st line
rawText = BulletChar & rawText
For LC = 1 To Len(rawText) - 1
'vbLF = Chr$(10)
'2 in a row
'1st is not changed to a bullet
If Mid(rawText, LC, 1) = vbLf Then
If Mid(rawText, LC + 1, 1) <> vbLf Then
rawText = Left(rawText, LC) _
& BulletChar _
& Right(rawText, _
Len(rawText) - (LC))
End If
End If
Next
End If
anyCell.Value = rawText
End If
Next
Application.ScreenUpdating = True
End Sub