how to add bullet formatting to excel text

[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.

 

[NickHK]

Whilst you can probably achieve the same with a RegEx, try this:

Private Sub CommandButton1_Click()
Dim AllLines() As String
Dim Counter As Long

AllLines = Split(Range("A1").Value, Chr(10))

For Counter = 0 To UBound(AllLines)
If Len(AllLines(Counter)) > 0 Then AllLines(Counter) = "-" &
AllLines(Counter)
Next

Range("A1").Value = Join(AllLines, Chr(10))

End Sub

NickHK

 

[chiaramonte.michael]

that did the trick! thanks man!
-Michael

Whilst you can probably achieve the same with a RegEx, try this:

Private Sub CommandButton1_Click()
Dim AllLines() As String
Dim Counter As Long

AllLines = Split(Range("A1").Value, Chr(10))

For Counter = 0 To UBound(AllLines)
If Len(AllLines(Counter)) > 0 Then AllLines(Counter) = "-" &
AllLines(Counter)
Next

Range("A1").Value = Join(AllLines, Chr(10))

End Sub

NickHK

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.- Hide quoted text -

- Show quoted text -