0

I am merging two cells in Excel 2016
=E2&" - "&F2

Now, the text in cell E2 is Normal - the text in cell F2 is Bold
However, when merging the cells the entire text is Normal

If you write in a cell you can choose to make part of the text bold. So two formattings in one cell is possible - but when merging?

Additional info:
After researching the problem, I believe it requires a bit of VBA-script. If you can help with a script that reads the length of first part and then bolds the last part of the text that should do it. However the complete formula is: =IF(Plan!F2<>"";Plan!E2&" - "&Plan!F2;Plan!E2)

The VBA function I have so far is:

Function boldIt(navn As String, ekstra As String)

Dim ln1 As Integer
Dim ln2 As Integer

ln1 = Len(navn)
ln2 = Len(navn) + Len(ekstra)

If (ln1 = ln2) Then
    boldIt = navn
Else
    boldIt = navn & " - " & ekstra
    boldTxt ln1, ln2
End If

End Function

Public Sub boldTxt(startPos As Integer, charCount As Integer)
    With ActiveCell.Characters(Start:=startPos, Length:=charCount).Font
        .FontStyle = "Bold"
    End With
End Sub

The function takes the content of the two text cells I want to combine
The text is merged correct and the sub-function is called(tested with msgBox)
However, the text is not bolded as I expected

| improve this question | | | | |
0

I don't have Excel 2016 to test, what I know:

Up to Excel 2013:

When you merge cells in Excel, you merge only their "space", but nothing else.
Content, formatting, border, fill ... will be kept from the first cell only, and is lost for all the others.
This is by design.

In Excel Online:

You can merge only ranges where maximum one cell contains information, in this case formatting is also transferred correctly for me.

| improve this answer | | | | |
  • I know it can be done with VBA - and that that it can be written directly into the cells in 2016 – Morten Repsdorph Husfeldt Apr 21 '17 at 10:26
0

I did it with a Sub :-)

This Sub loops through the columns, takes the strings of the two cells, combines the strings and add them to the target cell, while bolding the text of the second cell

Tnx to Máté Juhász for his pointers!

Sub boldIt()
Dim pos_bold As Integer
Dim celltxt As String

For i = 2 To 200000
    ' first cell will always be populated - if not - exit
    If (Range("Plan!E" & i).Value = "") Then Exit For

    ' if second cell is empty - take only first cell as normal txt
    If (Range("Plan!F" & i).Value = "") Then
        Range("Kalender!F" & i).Value = Range("Plan!E" & i).Value
    Else
        ' calculate start of bold text
        pos_bold = Len(Range("Plan!E" & i).Value) + 1

        ' create the string
        celltxt = Range("Plan!E" & i).Value & " - " & Range("Plan!F" & i).Value
        ' add string to field and add bold to last part
        With Worksheets("Kalender").Range("F" & i)
            .Value = celltxt
            .Characters(pos_bold).Font.Bold = True
        End With
    End If
Next i
End Sub
| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.