Format concatenate text

[dipkchopra]

I am trying to join text in two different cells using concatenate function / & operator
In the combined cell I want the text from second cell to appear in Bold while the text from first cell should continue to remain in normal font. Is there any way i could achieve this?

 

[Rick Rothstein]

I am trying to join text in two different cells using concatenate function / & operator
In the combined cell I want the text from second cell to appear in Bold while the text from first cell should continue to remain in normal font. Is there any way i could achieve this?

You cannot do that with a formula, but there is a VBA method available if you can make use of a coded solution. If you can, what cells are we talking about?

 

[dipkchopra]

Would love to have a VBA function which could work as excel formula (if possible)

Cells i am referring to are two simple cells (lets say A1 and A2)
A1 contains, say, "Hello"
A2 contains, say, "Dear"
When i Join these two in A3 i get "Hello Dear"
but what I wish is: "Hello Dear"

In practice my cells are spread over different sheets and are more than 2. But i think if you guide me for two cells in above example i should be able to be able to apply it in my case as well.
thanks in advance.

 

[Rick Rothstein]

Would love to have a VBA function which could work as excel formula (if possible)

Cells i am referring to are two simple cells (lets say A1 and A2)
A1 contains, say, "Hello"
A2 contains, say, "Dear"
When i Join these two in A3 i get "Hello Dear"
but what I wish is: "Hello Dear"

It is not a UDF (user defined function which can be used in a formula), rather, it is event code. Give it a try and see what you think (note that you do not need to bold the text in A2 for this code to work... it takes care of the bolding by itself)...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "A1" Or Target.Address(0, 0) = "A2" Then
    Range("A3") = Range("A1").Value & " " & Range("A2").Value
    Range("A3").Characters(Len(Range("A1").Value) + 2, Len(Range("A2").Value)).Font.Bold = True
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

Note: If you have multiple sheets involved, there is a way to do this using the ThisWorkbook code module rather than the worksheet module like I used above, but you would have to spell out, in detail, what the sheet names are and which cells on each of those sheets get concatenated and where that concatenation is to be outputted to.

 

[dipkchopra]

Thank a ton sir,
My problem is solved for now.
But surprises me that there is not excel solution to this (i mean without VBA)
nevertheless thank you once again