Forum Discussion

Rajni2022's avatar
Rajni2022
Copper Contributor
Aug 16, 2023

Editing Text in Each Line in a Cell

Dear Fellows

 

I have an excel database with more than 3000 rows. In each row, there is a columns which contains multiple lines of text in each cell (approx 10-12 lines). I mean, there are multiple lines in one cell. I wish to add "hypen or Asterisk" in beginning of each line. What is the best way to do so. It is too tedious to go to each line and add manually. Many thanks.

 

Regards

 

Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 17, 2023

    Rajni2022 

    Run this macro:

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