Announcement

Collapse
No announcement yet.

Prevent Concatenate Function from dropping font formatting

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment


    • #3
      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.

      Comment


      • #4
        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]
        Kris

        ExcelFox

        Comment


        • #5
          Re: Prevent Concatenate Function from dropping font formatting

          =TEXT(A1,"NumberFormatHere")&"-" &TEXT(B1,"NumberFormatHere")

          Comment


          • #6
            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
            OldPart - NewPart
            or different formatting in different parts of the result string
            OldPart - NewPart

            Comment


            • #7
              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.

              Comment


              • #8
                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.

                Comment


                • #9
                  Re: Prevent Concatenate Function from dropping font formatting

                  Ditto.
                  Re: post #2, as I stated.

                  VBA is the only option.
                  AAE
                  ----------------------------------------------------

                  Forum Rules | Message to Cross Posters | How to use Tags

                  Comment


                  • #10
                    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.
                    Last edited by AAE; August 27th, 2011, 11:00. Reason: delete quote

                    Comment


                    • #11
                      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
                      AAE
                      ----------------------------------------------------

                      Forum Rules | Message to Cross Posters | How to use Tags

                      Comment


                      • #12
                        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]
                        Kris

                        ExcelFox

                        Comment


                        • #13


                          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

                          Comment

                          Working...
                          X