Share via

Can I keep the formatting when concatenating?

Anonymous
2019-09-14T13:36:15+00:00

I am combining (concatenate) cells to form a complete description or sentence. I would like to know if there is a way to maintain or format a portion of the result based on the formatting of the original cell OR if there is a way to format it by formula or macro. 

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-09-14T19:22:05+00:00

    Hi Albanis

    That's amazing

    This is the macro I did

    **************************************************

    Sub JoinStrings_and_keep_Formating()

    Dim LRow, k, m, p As Long

    Dim Result As Range

    Dim strBold As String

    ''' This is the last occupied row in column X

    LRow = ActiveSheet.Cells(Rows.Count, "X").End(xlUp).Row

    For k = 2 To LRow

            Set Result = ActiveSheet.Cells(k, "AA")

            ''' concatenate values

            Result.Value = Trim(Cells(k, "Y").Value) & Chr(32) & Trim(Cells(k, "Z").Value) & Chr(32) & Trim(Cells(k, "X").Value)

            m = Len(Trim(Cells(k, "X").Value))  ''' String to be bolded Lenth

            p = InStr(Result.Value, Trim(Cells(k, "X").Value)) '' String to be bolded position

            Result.Characters(p, m).Font.Bold = True  ''' Bolding

            Result.Characters(p, m).Font.Underline = xlUnderlineStyleSingle  '''Underlining

    Next k

    MsgBox "Done"

    End Sub

    ***************************************************************************************

    But yours is amazing

    Could you give me some details of how you created it?

    How you collect this variables Prefix,  Mid, Name?

    I gave you Upvote *****

    Thanks a lot, in advance.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2019-09-14T18:31:20+00:00

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2019-09-14T14:42:33+00:00

    Hi JW, my name is Albanis

    The format is not concatenable, but what you want is possible with macros, I will work on an example for you

    Regards

    0 comments
  4. Anonymous
    2019-09-14T14:06:15+00:00

    As an alternative (if initial request is not possible)... 

    Could I (with macro) search col AA, bold the text that matches the text in the same row in col x?

    0 comments