Concatentate Join Identifier

[Guest]

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

 

[Guest]

Would Uppercase and Lowercase work?

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

 

[DOR]

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