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