Create text and keep formatting from range of cells

[jonybandana]

Hi everyone!
I have the following table:

Task	Owner	Date	Concat1 ("RESP" + Owner)	Concat2 ("FCST" + Date)	Concat3 (everything)
Do something.	Greg	24/10/2024	Owner: Greg	Forecast: 24-10-24	Do something. Owner: Greg Forecast: 24-10-24
Do this.	Mark	1/11/2024	Owner: Mark	Forecast: 01-11-24	Do this. Owner: Mark Forecast: 01-11-24
Do that.	Jen	15/1/2025	Owner: Jen	Forecast: 15-01-25	Do that. Owner: Jen Forecast: 15-01-25

I have this set up because I have to send minutes of our meetings at my job, and this makes the job of typing names and dates and saves me from typing "FORECAST" and "OWNER" a millon times every time I send a minute.

I also have a third column that concatenates everything, so I can just copy it over to work and will look like regular text, and it wont have "tab" characters between every "column", so it would look like this bulleted in Word:

• Do something Owner: Greg Forecast: 24-10-24
• Do this Owner: Mark Forecast: 01-11-24
• Do that Owner: Jen Forecast: 15-01-25

My only issue here is that this way I do not keep the formattin (The bolding in the last two columns), and I would like to keep it.

I looked into text boxes, but they don't really work like that. Is there any way I would get something like this instead?

• Do something Owner: Greg Forecast: 24-10-24
• Do this Owner: Mark Forecast: 01-11-24
• Do that Owner: Jen Forecast: 15-01-25

Thanks for any help you can give me!

 

[Micron]

Research indicates that this is not possible using cell formulas or functions. You would have to resort to vba. In F2 (?) down, you'd replace what you have with a vba function call. That function would concatenate col(A) row with "Owner " & col(B) & "Forecast " & col(C) along with the required spaces. The function would also format your string portions. This would also eliminate the need for the concat columns you have now. If you're willing to use vba this should be quite simple. You'd need to id the columns as they have no headers in your post.

 

[Micron]

Update: In all my years of dabbling in vba I never tried to bold parts of a string as I suggested. Turns out it is not possible. So your options are to
- use vba to bold part of your fully concatenated cell col(F)? so that you can copy that for your email text (and hope the formatting carries over)
- use vba to build your email body as html (not plain text) and send the message in one go (or just display it). This assumes you'd be using Outlook.

 

[Sequoyah]

Hi all, here is my attempt, it inserts the concatenated and formatted text in the column D. The code is to be pasted in the sheet module (right click on the sheet tab, choose view code, paste the following code).

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, Me.Range("A2:C" & Me.Cells(Me.Rows.Count, "A").End(xlUp).Row))
    
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        
        Dim cell As Range
        For Each cell In rng
            If Not Intersect(cell, Me.Columns("A:C")) Is Nothing Then
                Dim task As String
                Dim Owner As String
                Dim dateForecast As String
                Dim concat1 As String
                Dim concat2 As String
                
                task = Me.Cells(cell.Row, "A").Value
                Owner = Me.Cells(cell.Row, "B").Value
                dateForecast = Format(Me.Cells(cell.Row, "C").Value, "dd-mm-yy") ' Formatta la data
                
                Me.Cells(cell.Row, "D").ClearFormats
                concat1 = "Owner: " & Owner
                concat2 = "Forecast: " & dateForecast
              
                Me.Cells(cell.Row, "D").ClearFormats
                Me.Cells(cell.Row, "D").Value = Chr(149) & " " & task & " " & concat1 & " " & concat2
                                
                Dim startPos As Long
                startPos = Len(task) + 3
                Me.Cells(cell.Row, "D").Characters(startPos, Len(concat1) + 1).Font.Bold = True
                Me.Cells(cell.Row, "D").Characters(startPos + Len(concat1) + 2, Len(concat2)).Font.Bold = True
            End If
        Next cell
        
        Application.EnableEvents = True
    End If
End Sub

 

[Micron]

Looks OK but I just glanced over it. Since this is about sending emails, I have to wonder why not just automate that and do the formatting in the htmlBody of the email? That would sort of kill 2 birds with one stone so to speak. In other words, the code could create and send (or just display for review) the email with the body text formatted as per the requirements of this thread. That would eliminate the cell formatting code part, and the need to copy and paste the result of that code into the email.