Prevent Concatenate Function from dropping font formatting

  • Fellow Forum Members,
    I have a list of 200 outdated part numbers inside Column A. The font properties for all Column A numbers is setup to display all the numbers as RED and with a Strikthrough Line.


    In Column B, I have a list of the NEW Part Numbers that are going to replace the old part numbers. All these numbers are shown in black and with NO Strikethrough.


    When I use this Concatenate function in Cell C1:
    =Concatenate(A1," - ",B1)


    Excel drops the font formatting I had setup for the Column A numbers. How do I edit my Concatenate function so that it keeps the RED colored numbers aswell as the Strikthrough font property? In short, I need cell C1 to show the old part number as RED with strikthrough and also the New Part Number as black all within the C1 cell. Is using the TEXT function together with the Concatenate function the solution? Something like: =Concatenate(Text(A1)," - ",B1)). I tried it and it doesn't work.


    Any help will be greatly appreciated. Thanks.

  • Re: Prevent Concatenate Function from dropping font formatting


    It is not possible with a formula to retain formatting from other cells, at least not color and accents.


    You might explore trying to configure a custom format
    Getting the color in a custom format is do-able, but I'm not sure about the strike-through.

  • Re: Prevent Concatenate Function from dropping font formatting


    Thanks AAE,
    You say it is not possible with a formula to retain formatting from other cells. Do you think it's possible with a VBA script? I was playing around with one of two part numbers inside the same cell, and interestingly enough I was able to apply the color RED and a Strikethrough to one of the two part numbers inside the same cell. So I have a hunch that Excel is capable of doing it, if not with a formula then maybe using a VBA script. Can anyone out there help me develop such a VBA script? Thanks.

  • Re: Prevent Concatenate Function from dropping font formatting


    Hi,


    Try this


    [vb]Sub kTest()

    Dim r As Range, i As Long

    Set r = Range("a1").CurrentRegion.Resize(, 3)

    For i = 1 To r.Rows.Count
    With r.Cells(i, 3)
    .Value = r.Cells(i, 1) & " - " & r.Cells(i, 2)
    With .Characters(1, Len(r.Cells(i, 1)))
    .Font.Color = 255
    .Font.Strikethrough = 1
    End With
    End With
    Next

    End Sub[/vb]

  • Re: Prevent Concatenate Function from dropping font formatting


    Do you want the result to all have the same formatting (which matches that in column A
    [COLOR="#FF0000"]OldPart - NewPart[/COLOR]
    or different formatting in different parts of the result string
    [COLOR="#FF0000"]OldPart[/COLOR] - NewPart

  • Re: Prevent Concatenate Function from dropping font formatting


    Thanks to all who have posted in this thread.


    Krishnakumar:
    I tested out your code and it works nicely. However, I was wondering if you can make two changes. First, your VBA drops the leading zeros. So if I have 0075 in Column A or B it displays in Column C as "75". The VBA drops the leading zeroes which are generated using the costume FORMAT setting named Portugal ZipCode. Is it possible for your VBA to keep the leading zeroes? Also, instead of a dash shown in your code as " - ", I was trying to replace it with CHAR(10) which is suppose to give me a carriage return. However, it doesn't work. It would be awesome if you can change the dash with a CHAR(10) carriage return.



    Dave:
    I played around with your code:
    =TEXT(A1,"NumberFormatHere")&"-" &TEXT(B1,"NumberFormatHere")


    and I couldn't get it to produce the results I need. Don't understand what needs to be inserted inside of the quotes that say NumberFormatHere. Not clear to me.


    Mikerikson:
    What I need is different formatting in different parts of the result string. Like what is shown below (although I am having a problem displaying the strikethrough like you did in your post). However, I now want to avoid using the dash symbol and use the carriage return code instead "CHAR(10)"
    OldPart - NewPart


    I think the solution is the VBA code that Krishnakumar posted. But it needs to be fixed for dropping leading zeroes and to use a carriage return instead of a dash. Any help will be greatly appreciated. Thanks again to all who have posted.

  • Re: Prevent Concatenate Function from dropping font formatting


    If a cell has a formula, then all of it must be formatted the same.
    You could get your goal with VBA but there is no way to massage a cell with a formula to show different formats as indicated.

  • Re: Prevent Concatenate Function from dropping font formatting


    Krishnakumar,
    Your VBA seems to be the only way to accomplish this task. I would be very grateful if you can tweak your code so that it supports leading zeroes? Currently, numbers such as 0230, 0032, 0004 display incorrectly with no leading zeroes. The result is like this: 230, 32 and 4 which is incorrect.


    Replacing the " - " (dash) with a carriage return Char(10) will be icing on the cake. Any help will be greatly appreciated. Thanks.

  • Re: Prevent Concatenate Function from dropping font formatting


    binar,


    Please do not quote entire posts. When quoting follow these guidelines


    1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
    2. Quote ONLY the specific part of the post that is relevant - - not the entire post.


    This will keep thread clutter to a minimum and make the discussion easier to follow.
    Thanks

  • Re: Prevent Concatenate Function from dropping font formatting


    Hi,


    Sorry for late reply. Try


    [vb]Sub kTest()

    Dim r As Range, i As Long
    Dim OldPart As String
    Dim NewPart As String
    Set r = Range("a1").CurrentRegion.Resize(, 3)

    Const PartNumberLength As Long = 4 'adjust to suit

    For i = 1 To r.Rows.Count
    With r.Cells(i, 3)
    OldPart = String(PartNumberLength - Len(r.Cells(i, 1)), "0") & r.Cells(i, 1)
    NewPart = String(PartNumberLength - Len(r.Cells(i, 2)), "0") & r.Cells(i, 2)
    .Value = OldPart & Chr(10) & NewPart
    With .Characters(1, PartNumberLength)
    .Font.Color = 255
    .Font.Strikethrough = 1
    End With
    End With
    Next


    End Sub[/vb]

  • i want to vba code for Concatenate Function from dropping font formatting
    Ex. A1 = I want, A2=Only, A3=Five, A4=Chocolate
    Formula= Concatenate(A1,A2,A3,A4)
    Result = I want Only Five Chocolate
    i.e. = I want - Regular Font, Only Five - Bold, Chocolate - Italic
    give above solutions with example