Aug 26 2021 10:58 AM
Hello,
With the project I'm working on, I pull data from one document to put into Excel. The original document doesn't use underlines, but rather vertical bars as a kind of bracket. Then when I paste in Excel, I have to manually take those bars out and underline. For example, I want to paste "|Book Title|" and have it show up as "Book Title". Any help would be appreciated!
Aug 26 2021 11:20 AM
Here is a macro you can run afterwards, to change them in one go:
Sub Pipe2Underline()
Dim rng As Range
Application.ScreenUpdating = False
With Cells
Set rng = .Find(What:="|*|", LookAt:=xlWhole)
If Not rng Is Nothing Then
Do
rng.Value = Replace(rng.Value, "|", "")
rng.Font.Underline = xlUnderlineStyleSingle
Set rng = .FindNext(After:=rng)
Loop Until rng Is Nothing
End If
End With
Application.ScreenUpdating = True
End Sub
Aug 26 2021 11:55 AM - edited Aug 26 2021 12:02 PM
Actually, my mistake. It's working if the |book title| is the only thing in the cell, but it's not working if there is other text. I should have explained that before. So if it's only "|book title|", it works. But if it's "this is the |book title|" it doesn't
Aug 26 2021 12:03 PM
Change the line
Set rng = .Find(What:="|*|", LookAt:=xlWhole)
to
Set rng = .Find(What:="|", LookAt:=xlPart)
Aug 26 2021 12:11 PM
Aug 26 2021 01:08 PM
Sorry, I didn't think that through.
Sub Pipe2Underline()
Dim rng As Range
Dim p1 As Long
Dim p2 As Long
Application.ScreenUpdating = False
With Cells
Set rng = .Find(What:="*|*|*", LookAt:=xlWhole)
If Not rng Is Nothing Then
Do
p1 = InStr(1, rng.Value, "|")
p2 = InStr(p1 + 1, rng.Value, "|")
rng.Characters(p1 + 1, p2 - p1 - 1).Font.Underline = xlUnderlineStyleSingle
rng.Characters(p1, 1).Text = ""
rng.Characters(p2, 1).Text = ""
Set rng = .FindNext(After:=rng)
Loop Until rng Is Nothing
End If
End With
Application.ScreenUpdating = True
End Sub