how to format cells so they contain bullets
 

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.

how to format cells so they contain bullets
 

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

" wrote:

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.