Excel - Underline PART of a formula result 1

[Sabrina224]

I know this is a simple format thing . . . I just can't find the correct syntax -- example:

A1 = John Smith
A2="The man's name is "&A1
but I want the A1 result that appears in A2 to be the only text underlined so that it looks like:

The man's name is John Smith
----------


What am I missing? Thanks in advance!

 

[WalterContrata]

Sabrina224,

I can't find a way to do it without VBA. Maybe someone else can?

In VBA, if we record the act of underlining part of a cell that contains a constant string, we get something like

ActiveCell.Characters(Start:=10, Length:=5). _
Font.Underline = xlUnderlineStyleSingle

Using that, a little macro can be made to read the name from column 1 and write an appropriately formated constant string to column 2. But the macro will have to be run every time there is a change in the name column.

Assuming that the names are in column A, with no blank cells before the end of the column,

Option Explicit

Sub MakeNameColumn()
    Dim sh As Worksheet
    Dim rn As Range
    Dim iRow As Integer

    Dim sPrefix As String
    Dim sName As String
    
    Set sh = ActiveSheet
    sPrefix = "The man's name is"
    
    iRow = 1
    Do While sh.Cells(iRow, 1) <> ""
        sName = sh.Cells(iRow, 1)
        Set rn = sh.Cells(iRow, 2)
        ConcatenateAndUnderline rn:=rn, s1:=sPrefix, s2:=sName
        iRow = iRow + 1
    Loop
    
    Set rn = Nothing
    Set sh = Nothing
End Sub


Function ConcatenateAndUnderline(rn As Range, s1 As String, s2 As String)
    
    Dim iLen1 As Integer
    Dim iLen2 As Integer
    
    iLen1 = Len(s1)
    iLen2 = Len(s2)
    
    rn = s1 & " " & s2
    
    
    rn.Characters(Start:=iLen1 + 2, Length:=iLen2). _
        Font.Underline = xlUnderlineStyleSingle
End Function

Best Regards,
Walter

 

[GlennUK]

Hi Sabrina,

as you may have guessed by now, this is not a simple format thing. Walter has done a good job on doing you a VBA solution ( I didn't check it by the way ) ... is that good enough for you?

That's the only way to do it, unless you want to have markers in the cell below instead. This would mean using a non-proportional spaced font ( like Courier New ) for both cells, and using a formula to generate the markers:

A3=
=REPT(" ",LEN(A2)-LEN(A1))&REPT("-",LEN(A1))

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.