Announcement

Collapse
No announcement yet.

Prevent Concatenate Function from dropping font formatting

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

  • binar
    started a topic Prevent Concatenate Function from dropping font formatting

    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.

  • mohodnaren
    replied
    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

    Leave a comment:


  • Krishnakumar
    replied
    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]

    Leave a comment:


  • AAE
    replied
    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

    Leave a comment:


  • binar
    replied
    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

    Leave a comment:


  • AAE
    replied
    Re: Prevent Concatenate Function from dropping font formatting

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

    VBA is the only option.

    Leave a comment:


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

    Leave a comment:


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

    Leave a comment:


  • mikerickson
    replied
    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

    Leave a comment:


  • Dave Hawley
    replied
    Re: Prevent Concatenate Function from dropping font formatting

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

    Leave a comment:


  • Krishnakumar
    replied
    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]

    Leave a comment:


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

    Leave a comment:


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

    Leave a comment:

Working...
X