asked on

Concatenate strings with original parameters

Hi Experts,
I'd like to concatenate strings with original parameters (size, color, etc) in large file. Used the Concatenate function the original parameters disappears and the text will have unified look - see attached file. Could somebody help me? Every suggestion appreciated.
János

Rey Obrero (Capricorn1)

where is the file? and explain in details what is needed to be done.

kacor

ASKER

Sorry may be the file was not attached
Concatenate.xlsx

Rey Obrero (Capricorn1)

so, what is the problem, you already have the formula in the file?

kacor

ASKER

The resulted concatenated text has to have similar look like the original components.

Rey Obrero (Capricorn1)

do you mean separated? like space in between?
try

=CONCATENATE(A2," ",B2," ",C2)

kacor

ASKER

unfortunately my earlier idea was wrong. I tried to use the format painter but without success.

kacor

ASKER

using the suggested formula =CONCATENATE(A2," ",B2," ",C2) gives the same result.

kacor

ASKER

better said =CONCATENATE(A2;" ";B2;" ";C2)

Rey Obrero (Capricorn1)

sorry I am not following... what you really want to happen

kacor

ASKER

The resulted text must have the same parameters like the original parts. eg. in the ABCDEF string the C and D are green, the abcdef is red and in the resulted string ABCDEFabcdef12345 this letters must be similarly colored.

Rey Obrero (Capricorn1)

sorry, but that can't be done by just using formula.
you will need VBA but, it will be very complicated since you have to deal with every different formats of the cells content.

I will not dare doing this.

kacor

ASKER

thank you for your efforts :)

Bill Prew

Can I ask why you want to do this, what is the intended use?

~bp

kacor

ASKER

This is user's need better to review the data.

kacor

ASKER

The ColorIndex property returns or sets the color of the border, font or interior. But this is for the whole string in the cell. It is possible to color the characters in a string one by one and unfortunately I found nothing about.

Shums Faruk

If you want VBA to do this, then please copy paste below code and change your cell reference accordingly:

Sub Merge_Cells()
Dim x As Integer
Dim rngFrom1 As Range
Dim rngFrom2 As Range
Dim rngFrom3 As Range
Dim rngTo As Range
Dim lenFrom1 As Integer
Dim lenFrom2 As Integer
Dim lenFrom3 As Integer
Application.ScreenUpdating = False
  Set rngFrom1 = Cells(1, 1)
  Set rngFrom2 = Cells(1, 2)
  Set rngFrom3 = Cells(1, 3)
  
  
  Set rngTo = Cells(1, 5)
  lenFrom1 = rngFrom1.Characters.Count
  lenFrom2 = rngFrom2.Characters.Count
  lenFrom3 = rngFrom3.Characters.Count

  rngTo.Value = rngFrom1.Text & rngFrom2.Text & rngFrom3.Text

  For x = 1 To lenFrom1
    With rngTo.Characters(x, 1).Font
      .Name = rngFrom1.Characters(x, 1).Font.Name
      .Bold = rngFrom1.Characters(x, 1).Font.Bold
      .Size = rngFrom1.Characters(x, 1).Font.Size
      .ColorIndex = rngFrom1.Characters(x, 1).Font.ColorIndex
    End With
  Next x
  For x = 1 To lenFrom2
    With rngTo.Characters(lenFrom1 + x, 1).Font
      .Name = rngFrom2.Characters(x, 1).Font.Name
      .Bold = rngFrom2.Characters(x, 1).Font.Bold
      .Size = rngFrom2.Characters(x, 1).Font.Size
      .ColorIndex = rngFrom2.Characters(x, 1).Font.ColorIndex
    End With
  Next x
  For x = 1 To lenFrom3
    With rngTo.Characters(lenFrom1 + lenFrom2 + x, 1).Font
      .Name = rngFrom3.Characters(x, 1).Font.Name
      .Bold = rngFrom3.Characters(x, 1).Font.Bold
      .Size = rngFrom3.Characters(x, 1).Font.Size
      .ColorIndex = rngFrom3.Characters(x, 1).Font.ColorIndex
    End With
  Next x
Application.ScreenUpdating = True
End Sub

Open in new window

Please find attached workbook for your reference.

I added VBA in your topic so other experts can give you better code.
Concatenate.xlsm

kacor

ASKER

Hi Sums! I tested your suggestion and I found it works fast perfect. The only problem is: in my case the italic attribute isn't transferred but for the other attributes is perfect. Thanks for your great help!
wbr János

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kacor

ASKER

It's my mistake. I didn't read careful the first suggestion which didn't contain solution for italic :))) thanks, everything is ok.

kacor

ASKER

thanks for your support. my fried asked me for this and I felt this would be the right way but somehow I was not sure for this. thanks

Shums Faruk

You're welcome Sir! Glad I was able to help you.