Delete lines after a bullet

[cdmcnamara]

Hi,

I am attempting to write a macro that given a range of cells goes into each cell and deletes the text after the first bullet. An example of this would be:

The original cell contains:
---------------------

• The company had revenues of $100 mm in 2008
• The company is located in Mountainview, CA
• The company CEO is Eric Schmidt

---------------------

Upon executing the macro the cell is reformatted to:
---------------------

• The company had revenues of $100 mm in 2008

---------------------

Thanks in advance!

[jasoncw]

Here is a procedure that would work. You would just need to replace A1:A10 with your specified range. It could also work on selected cells by changing Range("A1:A10") to Selection.

Sub RemoveLineBreaks()
    Dim Rng As Range, Cell As Range
    Dim iPos As Integer
    Set Rng = Range("A1:A10")
    
    For Each Cell In Rng
        iPos = 0
        On Error Resume Next
        iPos = InStr(1, Cell.Text, Chr(13))
        On Error GoTo 0
        If iPos > 0 Then Cell = Left(Cell, iPos - 1)
    Next Cell
End Sub

HTH,
Jason

[cdmcnamara]

Jasoncw,

That doesn't seem to be working, the cell remains the same way it was before. Does it make a difference that their are lines after each bullet point?

The actual formatting is:
---------------------

* The company had revenues of $100 mm in 2008

* The company is located in Mountainview, CA

* The company CEO is Eric Schmidt

---------------------

Thanks in advance!

[shg]

Change Chr(13) to Chr(10)

[cdmcnamara]

That works if the first bullet is on the second line but there is a blank line prior to the first bullet, how can I account for this?

Thanks in advanced!

[shg]

I dunno -- what do you want it to do?

Posting a workbook with some examples and desired results would help.

[cdmcnamara]

The character is: ■

[shg]

Please delete the sensitive data and post a workbook.