+ Reply to Thread
Results 1 to 3 of 3

Concatentate Join Identifier

  1. #1
    Kiser
    Guest

    Concatentate Join Identifier

    I have two columns of text...columns A & B

    I want to combine the data in the two columns to form one column. Normally,
    I'd use the CONCATENATE function. However, I want to record where the join
    occurred without inserting extra characters.

    For instance, if I color the text in column A red and I color the text in
    column B blue; when I combine them, I'd like for the text to maintain its
    original color so that the first portion of the string is red, the following
    protion is blue. Alternatively I could do that with a font change at the
    join point.

    How could I achieve that?

    Thanks


  2. #2
    Elkar
    Guest

    RE: Concatentate Join Identifier

    Would Uppercase and Lowercase work?

    =CONCATENATE(UPPER(A1),LOWER(B1))


    "Kiser" wrote:

    > I have two columns of text...columns A & B
    >
    > I want to combine the data in the two columns to form one column. Normally,
    > I'd use the CONCATENATE function. However, I want to record where the join
    > occurred without inserting extra characters.
    >
    > For instance, if I color the text in column A red and I color the text in
    > column B blue; when I combine them, I'd like for the text to maintain its
    > original color so that the first portion of the string is red, the following
    > protion is blue. Alternatively I could do that with a font change at the
    > join point.
    >
    > How could I achieve that?
    >
    > Thanks
    >


  3. #3
    DOR
    Guest

    Re: Concatentate Join Identifier

    In the unlikely event that you are prepared to allow the cells
    containing the concatenated values to be converted to actual values
    rather than remain as formulas, the following VBA rountine will color
    the values as you require, assuming you first select the range to be so
    converted and colored:

    Sub ColorCat()
    Dim Color1 As Long
    Dim Color2 As Long
    Dim Lgt1 As Integer
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    For Each cell In Selection
    Color1 = cell.Offset(0, -2).Font.Color
    Color2 = cell.Offset(0, -1).Font.Color
    Lgt1 = Len(cell.Offset(0, -2))
    cell.Font.Color = Color2
    cell.Characters(1, Lgt1).Font.Color = Color1
    Next cell
    End Sub

    This first converts the contents of the selected cells to values and
    then colors them according to the colors and lengths of the values in
    the two adjacent columns. Note that it does no validation whatsoever
    regarding the lengths of the values in any of the cells, or the shape
    of the selected range, or whether the contents are in fact the
    concatenation of the two columns to the left. Nevertheless, it might
    be a base from which you can start.

    Unfortunately, it appears that you cannot have two different colors for
    the result of a formula; the Uppercase/Lowercase solution that was
    proposed may be your best bet if you must retain the formulas.

    HTH

    Declan O'R


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1