Format (bold, etc) concatenated items

**erikilacad** · 09-09-2009, 01:51 PM

Hi everyone!

I need help on formatting concatenated items.

Say, cell A1 contains a text, "dog", and is in bold format.

I'll type in cell B1:

="I have a "&A1&"."

which will return: "I have a dog."

What I want actually for cell B1 to show is: "I have a dog."

I can't seem to figure out how to do it. Is it even possible? Help!! Thanks

Erik

**shg** · 09-09-2009, 03:07 PM

Can't be done; you cannot, via formula or VBA, separately format (bold, italic, underline, ...) different parts of the results of a formula.

**GuruWannaB** · 09-09-2009, 03:27 PM

I hope this isn't hijacking...but it is a question about a possible answer to the question posed.

If "I have a " is in A1 and "Dog." is in B1...could you not have VBA code that you could initiate after you select C1 that would identify the string length of A1 and B1 seperately, combine the two texts together - plugging it into the active cell (c1) and then using the known originating string lengths change the formatting of just part of the resulting string? (BTW...I live for RUN-ON questions=)

Im still new to VBA...but I swear my brain is IBM...

**shg** · 09-09-2009, 03:31 PM

You could indeed, but that wouldn't be a formula.

**GuruWannaB** · 09-09-2009, 03:33 PM

ahh...indeed. Thanks for clarifying my oversight of the issue! Thus why I'm still a wannab!

**erikilacad** · 09-09-2009, 11:44 PM

Originally Posted by shg

Can't be done; you cannot, via formula or VBA, separately format (bold, italic, underline, ...) different parts of the results of a formula.

Thanks.

But is there any other way to get my desired output, aside from concatenation? Here's the exact thing I want:

Date of Event: [B1]
Venue: [B2]

(In cell D1)
We are pleased to submit our quotation for your event on [B1] to be held in [B2].

Whereas, [B1] and [B2] are formatted to be bold.

Thanks really.

**Paul** · 09-10-2009, 12:10 AM

Hi Erik,

Using VBA you can do this. The example below starts off by concatenating your static text with the values from B1 and B2, puts it into D1 and then formats D1 to bold the two input values.

It is triggered by the Worksheet_Change event only when you change either cell B1 or B2. If either is blank, the code does not run. The code also assumes that your date will be 10 characters long (mm/dd/yyyy). The code will need to be adjusted if that is not the case.

To use the code, right-click on the sheet tab on which you want the code to run, then select View Code. Copy the code shown below and paste it into the VB Editor window. Close the VB Editor and make a change to B1 or B2.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myStr1 As String, myStr2 As String, myStr3 As String
myStr1 = "We are pleased to submit our quotation for your event on "
myStr2 = " to be held in "
myStr3 = "."

If Not Intersect(Target, Range("B1:B2")) Is Nothing Then
    If Range("B1") <> "" And Range("B2") <> "" Then
        With Range("D1")
            .Value = myStr1 & Format(Range("B1").Value, "mm/dd/yyyy") & _
                     myStr2 & Range("B2").Value & myStr3
            .Characters(58, 10).Font.Bold = True
            .Characters(83, Len(Range("B2").Value)).Font.Bold = True
        End With
    Else
        Range("D1").Value = ""
    End If
End If
End Sub

Hope that helps!

**erikilacad** · 09-10-2009, 01:52 PM

It did work! Thanks so much.

I don't know how you did it, but it actually worked. I know a little (very little) coding in Visual Basic, but I don't know the stuff you used in this code.

But it did get the job done, thanks again!

Erik

**clemsoncooz** · 12-27-2011, 12:30 PM

VeryCool!!

OK - yes I am a noob at this. I want to do this exact same thing except not as complicated (but yet I still can not figure it out).

All I need to do is something similar BUT only one item is to be bold. But that bold item will change in charater length, so counting where to begin the bold (like shown above = 58 character) doesnt work for me. I need to join (3) items where the second itme is the one to be bold and changes.

Problem:
A flat fee of $11,750.00 is agreed upon for the delivery of the services.

This dollar amount changes any where from $1.00 to $999,999.00

Results to be placed in A155.