excelvba

How to remove (Underlined/ Strikethrough of characters) in Excel cells


Delete an underlined string

enter image description here

I am researching how to remove underscore characters and keep only characters that do not have underscores in each Excel cell. For example, if the cell value is multi-line and each line has an underscore, it should be removed. Thank you to the esteemed community.


Solution

  • This version will split the cell content by lines iterating between each other and check if one line character is underlined. According to that, the line will be removed (everything happening in two arrays). The result will be returned two columns to the right of original one. If you like the way you should only replace targetCell.Offset(, 2).value with targetCell.value:

    Sub demo_()
        Dim target As Range
        
        For Each target In Range("A2:B10").cells
            removeUnderlined target
        Next
        
    End Sub
    
    Sub removeUnderlined(targetCell As Range)
      Dim arr, nrCh As Long, arr1, i As Long, outTxt As String
      arr = Split(targetCell.value, vbLf) 'creating an array of cell lines
      
      If UBound(arr) = 0 Then 'if only a single line:
        If targetCell.Characters(1, 1).Font.Underline = xlUnderlineStyleSingle Then
            outTxt = ""      'the line will be removed
        Else
            outTxt = arr(0) 'the line text will be kept
        End If
        targetCell.Offset(, 2).value = outTxt: Exit Sub 'return the line
      Else
        For i = 0 To UBound(arr)
            nrCh = nrCh + Len(arr(i))
            If targetCell.Characters(nrCh - 1, 1).Font.Underline = xlUnderlineStyleSingle Then
                If Not IsArray(arr1) Then
                    arr1 = filter(arr, arr(i), False)   'create a new array without the underlined element
                Else
                    arr1 = filter(arr1, arr(i), False) 'remove the underlined element
                End If
            End If
        Next i
      End If
      If IsArray(arr1) Then
        outTxt = Join(arr1, vbLf) 'return the joined array
      Else
        outTxt = Join(arr, vbLf)  'return the joined array
      End If
      
      targetCell.Offset(, 2).value = outTxt 'return outTxt
      End Sub
    

    If the range to be processed is huge, I can supply a much faster version. I mean, it will work only in memory, placing the return in a third array and only dropping its content at once, at the end of the code.

    Edited:

    The next version is very fast, processing only in memory and only dropping the final array content at once, of the code end:

    Sub demo__()
        Dim rng As Range, target As Range, colNo As Long, rowNo As Long, arrFin
        
        Set rng = Range("B1:C6") 'use here the real range you need processing
        arrFin = rng.Offset(, 2).value 'placing the range in an array (just for testing...)
          'if you want to overwrite existing you should use arrFin = rng.Value
        For Each target In rng.cells
            colNo = target.column - rng.column + 1 'calculating the target column of rng
            rowNo = target.row - rng.row + 1       'calculating the target row of rng
            arrFin(rowNo, colNo) = removeUnderlined_(target) 'load the appropreate array element!
        Next
        'drop the final array content at once:
        rng.Offset(, 2).value = arrFin
    End Sub
    
    Function removeUnderlined_(targetCell As Range) As String
      Dim arr, nrCh As Long, arr1, i As Long, outTxt As String
      arr = Split(targetCell.value, vbLf) 'creating an array of cell lines
      
      If UBound(arr) = 0 Then 'if only a single line:
        If targetCell.Characters(1, 1).Font.Underline = xlUnderlineStyleSingle Then
            outTxt = ""      'the line will be removed
        Else
            outTxt = arr(0) 'the line text will be kept
        End If
        removeUnderlined_ = outTxt: Exit Function 'return only a line or null string
      Else
        For i = 0 To UBound(arr)
            nrCh = nrCh + Len(arr(i))
            If targetCell.Characters(nrCh - 1, 1).Font.Underline = xlUnderlineStyleSingle Then
                If Not IsArray(arr1) Then
                    arr1 = filter(arr, arr(i), False)  'remove the underlined element
                Else
                    arr1 = filter(arr1, arr(i), False) 'remove the underlined element
                End If
            End If
        Next i
      End If
      If IsArray(arr1) Then
        outTxt = Join(arr1, vbLf) 'return the joined arr1
      Else
        outTxt = Join(arr, vbLf) 'return the joined arr
      End If
      
      removeUnderlined_ = outTxt 'returning the processed cell output
    End Function
    

    I practically transformed the used processing Sub in a Function able to return the necessary string and only determined the final array row and column to fill the array elements appropriately.

    If something not clear enough, please do not hesitate to (specifically) ask for clarifications. Even if I think that my comments should be enough for a good understanding of the code...