Concatenate or copy cells keeping the original texts format, character by character.

Hi folks.

I've been trying to develop a function to concatenate or copy cells keeping original formats character by character. For example: cell 1 = a_ijⁿ and cell 2 = .b_uv and cell 3 = .s^m and I need: a_ijⁿ.b_uv.s^m
But the main problem is that I can't set the range of characters within the concatenated text that should be formated with an specific style just because I don't know were is it. For example, in cell 1 "a" could be "ResConc" or "ResAco" or others with diferente lenghs,"i" could be 243 or 1 and etc... So the problem is to set, for example: "...Characters(Start:=???, Length:=????).Font.Subscript". I need to find some way to identify each character style on the original cells and then transfer the character's styles one by one to the concatenated string. Please, help this beginner.

Hi andrecamapum,

Excellent question. From my limited research today, your EXACT request can not be done. That is because a function for a cell, can change the VALUE for the cell, but NOT the FORMAT.

I have an alternative, that may do what you want. It involves putting SECRET TEXT CODES in cells that tell a Macro what cells to concatenate, and where the concatenated text should go (which cell). the SECRET CODES can be hidden using custom format (;; [ 3 semi-colons] so the look and feel of your spreadsheet is not destroyed.

The code can be invoked be one or more of:
a. Command Button
b. Worksheet_Change() event
c. Worksheet_Activate() event

See the attached file and the code follows:
Sheet Module Code (if Change and/or Activate Events are desired):

Please Login or Register  to view this content.

Ordinary Module Code (such as Module1):

Please Login or Register  to view this content.

Lewis

Hi LJMetzger, thank you for your help. I downloaded the excell sheet and I saw that the results of the merge string were exactely the ones I were looking for. I also could understand well how the coed works on the sheet but understand the code by itself will demand more time from cause I'm such experienced in VBA.

Anyway, I was wondering if could be possible to insert a custom concatenate function that calls a sub to make an array of character styles from all the original cells to be merged, and when I press enter in the cell with that function, it will be the event to start your code, but in this way, the cells of origin would not be specified on your code, because I've already saved the characters styles, and the detination cell also would'nt be declared, cause it would be same cell I've just pressed enter. By this way the code could work pretty much as a function. in a cell I'll show you what I was thinking about. I think I've been confused in my explanation so I'll show you the code I was working on before asking for help, I ask for your pardon if its to simple and full bugs. There it goes:

Function ConcatFormat(ParamArray values() As Variant) As String
Dim concat As String
Dim rng As Variant
' Cycle of different indicated parameters
For Each rng In values
' Verify if was indicated a Range
If TypeOf rng Is Range Then
Dim r As Range
Set r = rng
' New cycle among the Range‘s cells
Dim cell As Range
Dim NumCaracter As Double
Dim IndCel As Double
Dim IndCaracter As Double
IndCaracter = 0
For Each cell In r
concat = concat + cell.Value
‘-----Here I call the routine 1
Next
End If
Next
' Set the value to the function
ConcatFormat = concat
End Function
…………..’ here, when I press enter it will be the event to call the routine 2

Routine 1

Dim FontName() As String
Dim FontStyle() As String
Dim FontSize() As Integer
Dim FontStrikethrough() As Boolean
Dim FontSuperscript() As Boolean
Dim FontSubscript() As Boolean
Dim FontOutline() As Boolean
Dim FontShadow() As Boolean
Dim FontUnderline() As Variant
Dim FontColorIndex() As Integer
Dim FontBold() As Boolean
Dim FontItalic() As Boolean

NumCaracter = Len(cell.Value)
For IndCel = 1 To NumCaracter Step 1
IndCaracter = IndCaracter + 1
……………… .’for me was sufficient to verify the subscripted and superscripted characters, but I was thinking that the routine could help others that are looking for bold or italic characters for example (I saw some people in internet asking help for that in some forums).
FontName(IndCaracter) = rng.Characters(IndCel, 1).Font.Name
' FontStyle(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.FontStyle
' FontSize(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.Size
' FontStrikethrough(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.Strikethrough
' FontSuperscript(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.Superscript
' FontSubscript(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.Subscript
' FontOutline(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.OutlineFont
' FontShadow(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.Shadow
' FontUnderline(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.Underline
' FontColorIndex(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.ColorIndex
' FontBold(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.Bold
' FontItalic(IndCaracter) = rng.Characters(Start:=IndCel, Length:=1).Font.Italic
Next

Routine 2

At first the destination cell will be defined as the last active cell that was the one in wich we inserted the function ConcatFormat.

If the variables FontSubscript(), FontColorIndex(), FontBold() etc… were defined just as to keep their last values after the routine 1 execution, for each IndCharacter value, so, we now could do:

For i=1 to IndCharacter step 1

Range(DestinationCell).Characters(Start:=i, Length:=1).Font.Superscript = FontSuperscript(i)
‘(and etc…)
Next

Does it make any sense?

Thank you for your patience.

I'm having problems implementing what I think you want. As far as I know, the reference to the destination cell, can't be in the destination cell due to value/formula/macro conflict restrictions.

I think no matter what we do, we need to have a function that references:
a. The destination cell
b. The source cell(s)

The ParamArray values() As Variant input structure that you suggested,could make things a little neater, but I'm afraid my skill level wouldn't be good enough to solve the problem the way you like.

Lewis

Metzger thank you for your efforts. At first I'm surprised that you could get what I was meaning in my such bad English and total disorganization of the last post. I'll try to do better now. I tried the ParamArray values() As Variant input structure, and it worked well to the concatenation of the texts. I wasn't knowing how to capture styles from character by character in a string, but now, from yours routine, I understood how to do that. But I don't know yet how to save those style's values in an variable that will not lose them after the execution of the function. My last post was surely confused, but I was wanting to mean that, once pressed the "enter" on the destination cell, It was not going to be the active cell anymore, so I just need to keep the reference of it after the function ended it's execution (pressing "enter" will be only the starting event). Here I may need additional studies since I don't know how to do this yet. Concerning to the source cells they are not the same destination cells. About this, the function is working well. But again, I just need to keep those references after the end of the function. And what your routine does that I can't wonder how to do that by now in the structure I propose is exactly to keep the references "these sources" ----> "these destinations". The structure that I proposed can do this just once, but yours do it by every change in source cells. I thought about to create a kind of array of [destination cells (for example: "those that contains the formula 'ConcatFormat"), sources ranges] for each style type. But if I call this in every event on the sheet it will surely compromise its efficiency. If I could do it, I could use the last part of yours routine and set the styles to each character of the destination cells. I'll try to complete my studies on the theme and thank you for your help.