Changing Brackets to Underline

%3CLINGO-SUB%20id%3D%22lingo-sub-2689121%22%20slang%3D%22en-US%22%3EChanging%20Brackets%20to%20Underline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2689121%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EHello%2C%3C%2FP%3E%3CP%20class%3D%22_1qeIAgB0cPwnLhDF9XSiJM%22%3EWith%20the%20project%20I'm%20working%20on%2C%20I%20pull%20data%20from%20one%20document%20to%20put%20into%20Excel.%20The%20original%20document%20doesn't%20use%20underlines%2C%20but%20rather%20vertical%20bars%20as%20a%20kind%20of%20bracket.%20Then%20when%20I%20paste%20in%20Excel%2C%20I%20have%20to%20manually%20take%20those%20bars%20out%20and%20underline.%20For%20example%2C%20I%20want%20to%20paste%20%22%7CBook%20Title%7C%22%20and%20have%20it%20show%20up%20as%20%22%3CU%3EBook%20Title%22.%3C%2FU%3E%20Any%20help%20would%20be%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2689121%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2689207%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20Brackets%20to%20Underline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2689207%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1138697%22%20target%3D%22_blank%22%3E%40cmcneal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro%20you%20can%20run%20afterwards%2C%20to%20change%20them%20in%20one%20go%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20Pipe2Underline()%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20With%20Cells%0A%20%20%20%20%20%20%20%20Set%20rng%20%3D%20.Find(What%3A%3D%22%7C*%7C%22%2C%20LookAt%3A%3DxlWhole)%0A%20%20%20%20%20%20%20%20If%20Not%20rng%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Do%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20rng.Value%20%3D%20Replace(rng.Value%2C%20%22%7C%22%2C%20%22%22)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20rng.Font.Underline%20%3D%20xlUnderlineStyleSingle%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20rng%20%3D%20.FindNext(After%3A%3Drng)%0A%20%20%20%20%20%20%20%20%20%20%20%20Loop%20Until%20rng%20Is%20Nothing%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20With%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2689288%22%20slang%3D%22en-US%22%3ERE%3A%20Changing%20Brackets%20to%20Underline%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2689288%22%20slang%3D%22en-US%22%3Eko%3C%2FLINGO-BODY%3E
New Contributor

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!

7 Replies

@cmcneal 

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

@Hans Vogelaar 

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

@cmcneal 

Change the line

        Set rng = .Find(What:="|*|", LookAt:=xlWhole)

to

        Set rng = .Find(What:="|", LookAt:=xlPart)

 

Sorry to ask for more help, but with that line changed, now it's underlining all the text in the cell, instead of only what was between the brackets

@cmcneal 

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