Underline a Specific Text in a Formula

[TDeatherage1]

I am trying to underline a text in a formula for a Construction contract application.

See formula below:
="Paragraph b hereof, for the construction of the Project: "&Input!C2&", "& Input!C4&", "&Input!C5&" in accordance with the contract dated the "&Input!C31&" , between Owner and Contractor, and the general and special conditions of that contract, and in accordance with the drawings, specifications and addenda for the construction."

Input!C2 is Sierra Pacific Surgical
Input!C4 is 8105 Saratoga Way
Input!C5 is El Dorado Hills, CA 95762
Input!C31 is 21st day of May 2024

This is what I want it to look like:
Paragraph b hereof, for the construction of the Project: Sierra Pacific Surgical, 8105 Saratoga Way, El Dorado Hills, CA 95762 in accordance with the contract dated the 21st day of May 2024 , between Owner and Contractor, and the general and special conditions of that contract, and in accordance with the drawings, specifications and addenda for the construction.

Right now I have inserted a line (shape) and that's sufficient for printing. However, every time I change the address, if it's shorter or longer, I have to change the length of the line.

The goal is to be able to change the Input Cells to any Project Name and Address & the Contract signed date and still have it underlined the exact length of the Input.

 

[dreid1011]

If VBA is an option, give this a try:
Update your sheet names and the output cell for the string.

Private Sub InsertText()
Dim s1, s2, s3, s4, cs, o1 As String
Dim wsIn, wsOut As Worksheet
Dim slen1, slen2 As Long

Set wsIn = Sheets("Input")
Set wsOut = Sheets("Output") 'update worksheet name

With wsIn
    s1 = wsIn.Range("C2").Value
    s2 = wsIn.Range("C4").Value
    s3 = wsIn.Range("C5").Value
    s4 = wsIn.Range("C31").Value
End With

cs = s1 & ", " & s2 & ", " & s3
slen1 = Len(cs)
slen2 = Len(s4)

o1 = "Paragraph b hereof, for the construction of the Project: " _
& cs & " in accordance with the contract dated the " & s4 & ", " _
& "between Owner and Contractor, and the general and special " _
& "conditions of that contract, and in accordance with the " _
& "drawings, specifications and addenda for the construction."


With wsOut.Range("A1") 'update cell where string is located
    .Value = o1
    .Characters(Start:=58, Length:=slen1).Font.Underline = True
    .Characters(Start:=58 + slen1 + 43, Length:=slen2).Font.Underline = True
End With

End Sub

 

[Fuji]

I am trying to underline a text in a formula for a Construction contract application.

If the cell is the result of a formula or only numeric value, you can't edit the cell partially, so it needs to be converted to string.
This will convert the formula to string and format the cell.
Select the cell(s) and run the code.

Sub test()
    Dim r As Range, myList, e, s$
    For Each r In Selection
        myList = GetRange(r)
        If IsArray(myList) Then
            r.Value = r.Value
            For Each e In myList
                r.Characters(InStr(r, e), Len(e)).Font.Underline = True
            Next
        End If
    Next
End Sub

Function GetRange(c As Range)
    Dim x, e, s$, n&, myList()
    s = Mid$(c.Formula, 2)
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[*/^><=()&,+-]"
        x = Split(.Replace(s, Chr(2)), Chr(2))
    End With
    For Each e In x
        If TypeOf Evaluate(e) Is Range Then
            n = n + 1
            ReDim Preserve myList(1 To n)
            myList(n) = Evaluate(e)
        End If
    Next
    If n Then GetRange = myList
End Function

Note;
Above doesn't work If any formula contains link to the other workbook.