Concatenate cells but keep formatting of text from source cells

[JULIANAo]

I have been trying to concatenate cells from a different master sheet into one cell for a label.
the problem is that some parts of the text are bold (for allergens) and when I finish it erases all the formatting from original cell.
I've tried several different macros and nothing works. Please help me!! I've tried everything I could search or think of.

My formula and what I get:

what I need:

 

[RoryA]

You cannot format the font of parts of a formula result.

 

[JULIANAo]

You cannot format the font of parts of a formula result.

The original cells are already formatted I was hoping to keep the formatting not re-do it (if possible)

 

[Peter_SSs]

Welcome to the MrExcel board!

As Rory has said, what you are trying to achieve is not possible using a formula.
It may be feasible with a macro. If you wanted to investigate that, the easiest way to bold the allergens would be if there is, or could be made, a list of such allergens. Is that available or possible to construct?

 

[JULIANAo]

Welcome to the MrExcel board!

As Rory has said, what you are trying to achieve is not possible using a formula.
It may be feasible with a macro. If you wanted to investigate that, the easiest way to bold the allergens would be if there is, or could be made, a list of such allergens. Is that available or possible to construct?

Yes I have one already actually

 

[Peter_SSs]

Yes I have one already actually

Ok, so ..

1. Where (sheet name and range/column) is the formula you quoted in post 1?
2. Can you confirm that these formulas can be replaced by the formula results as constants? Reason is we still cannot highlight (bold etc) the results while it is still a formula.
3. Where (sheet name and range/column) is the list of allergens?

 

[JULIANAo]

Ok, so ..

1. Where (sheet name and range/column) is the formula you quoted in post 1?
2. Can you confirm that these formulas can be replaced by the formula results as constants? Reason is we still cannot highlight (bold etc) the results while it is still a formula.
3. Where (sheet name and range/column) is the list of allergens?

This is the master sheet where I keep all the information I need for the labels
L3:L158 contains all the information that I have concatenated on sheet 2(APP) and so on for labels.
everything on Column L is already formatted how I need it. (list of allergens column P3:P10)

Below is sheet 2(APP) where I am trying to concatenate the ingredients from sheet 1 (L2:L158) to create labels.
The formula is on E35.
I have changed the formula to refer directly to the cells instead of using LOOKUP.
Does that help keeping the formatting from master sheet?

is this enough information?
Thank you so much for your help!:D

 

[Peter_SSs]

Thanks for the additional information. It didn't address question 2 though. As has been noted a couple of times, you cannot achieve formatting of individual parts of a formula result while it is still a formula.

I also mentioned earlier that "the easiest way to bold the allergens would be if there is, or could be made, a list of such allergens." That is, not trying to copy the formatting from column L of 'Ingredients' but simply finding any allergen names in the resulting text in E35 (merged I note) and formatting them there.

So this suggestion below replaces the formula in E35 with the formula result**, then looks through it for any words that match column P of 'Ingredients' and bolds them. See if this might suffice or at least be heading in a direction that might be some use to you. Please test in a copy of your workbook.

Sub BoldAllergens()
  Dim RX As Object, M As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  With Sheets("Ingredients")
    RX.Pattern = "\b(" & Application.TextJoin("|", 1, .Range("P3", .Range("P" & Rows.Count).End(xlUp)).Value) & ")\b"
  End With
  With Sheets("APP").Range("E35")
    .Value = .Value
    For Each M In RX.Execute(.Value)
      .Characters(M.firstindex + 1, Len(M)).Font.Bold = True
    Next M
  End With
End Sub

**
One option might be to leave the formula in E35 and have my code copy that formula result to a similar merged cell elsewhere on the sheet, format it there & use that for your label.
That could be set up to repeat the process any time the formula result in E35 changes.

 

[JULIANAo]

Thanks for the additional information. It didn't address question 2 though. As has been noted a couple of times, you cannot achieve formatting of individual parts of a formula result while it is still a formula.

I also mentioned earlier that "the easiest way to bold the allergens would be if there is, or could be made, a list of such allergens." That is, not trying to copy the formatting from column L of 'Ingredients' but simply finding any allergen names in the resulting text in E35 (merged I note) and formatting them there.

So this suggestion below replaces the formula in E35 with the formula result**, then looks through it for any words that match column P of 'Ingredients' and bolds them. See if this might suffice or at least be heading in a direction that might be some use to you. Please test in a copy of your workbook.

Sub BoldAllergens()
  Dim RX As Object, M As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = True
  With Sheets("Ingredients")
    RX.Pattern = "\b(" & Application.TextJoin("|", 1, .Range("P3", .Range("P" & Rows.Count).End(xlUp)).Value) & ")\b"
  End With
  With Sheets("APP").Range("E35")
    .Value = .Value
    For Each M In RX.Execute(.Value)
      .Characters(M.firstindex + 1, Len(M)).Font.Bold = True
    Next M
  End With
End Sub

**
One option might be to leave the formula in E35 and have my code copy that formula result to a similar merged cell elsewhere on the sheet, format it there & use that for your label.
That could be set up to repeat the process any time the formula result in E35 changes.

OMG!!! thanks it works perfectly!
I have done what you suggested which is copy the result to elsewhere and formatted it there.
Might I ask if you could help me as to how I go about "set up to repeat the process any time the formula result in E35 changes"?

 

[Peter_SSs]

Might I ask if you could help me as to how I go about "set up to repeat the process any time the formula result in E35 changes"?

Sure. Assuming the original formula is still in E35:
1. Right click the 'APP' sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Edit the code to reflect where you have made the new merged area. I have used M3 so just edit that (twice) in the code.
4. Close the Visual Basic window & test by making whatever change you need to to get a different result in E35.

Private Sub Worksheet_Calculate()
  Dim RX As Object, M As Object
  
  If Range("E35").Value <> Range("M3").Value Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    RX.IgnoreCase = True
    With Sheets("Ingredients")
      RX.Pattern = "\b(" & Application.TextJoin("|", 1, .Range("P3", .Range("P" & Rows.Count).End(xlUp)).Value) & ")\b"
    End With
    With Range("M3")
      .Font.Bold = False
      .Value = Range("E35").Value
      For Each M In RX.Execute(.Value)
        .Characters(M.firstindex + 1, Len(M)).Font.Bold = True
      Next M
    End With
  End If
End Sub