Concatenate and bold - Excel 2007

[preqin]

I have a simple concatenate in Excel...

=CONCATENATE(A1, ", ", A2)

Which produces:

This is, my text

Now, I want 'my text' i.e. the contents of A2 to be in bold.

I've played with the TEXT() function before, as per this page:
http://www.techonthenet.com/excel/formulas/text.php

To get Excel to play nicely with date formats and stuff, however, my research has come to a dead end with Bold/Italic - with everyone suggesting that VBA is the only solution.

As I don't feel overly comfortable using VBA is there any other solution? (If not, is this a VBA 101 task or an advanced task?)

 

[Yard]

Not a brackets issue, more like a language issue. The With statement tells the code what to apply an instruction to. The following line tells it what that instruction is.

The + sign is a mathematical operator, so makes no sense in an expression like that!

I like your style though; trial and error is my best friend too...

 

[preqin]

Ok, I've made some progress:

Sub BoldPartText()
Dim Part1Len, Part2Len, DividerLen As Integer
Dim Divider As String
Part1Len = Len(Range("A1")) + 1
Part2Len = Len(Range("B1"))
Divider = ", "
DividerLen = Len(Divider)

Range("C1") = Range("A1") & Divider & Range("B1")
With Range("C1").Characters(Start:=1, Length:=Part1Len).Font
        .FontStyle = "Bold"
End With
End Sub

I previously thought the with part was where the concatenating was occuring, I now see that

Range("C1") = Range("A1") & Divider & Range("B1")

is actually what produces the string, and the WITH part determines what part of the string to format, and how to format it.

One odd quirk - it seems that if I run it and then run it again it makes the whole string bold!! - even if I delete said string it still makes the whole lot in C1 bold! - If I edit C1 to be D1 and run it, it's fine in D1, run it again and it bolds all of D1.

 

[Yard]

After the

DividerLen = Len(Divider)

line, add:

Range("C1").Clear

 

[preqin]

Thanks Yard - that worked a treat.

I definitly think I've actually learnt something about VBA today rather than simply copy/paste be a robot - which is good

 

[Yard]

Cool, I like it when that happens.

 

[ewalters4]

I liked the code. Simple and to the point. I have a similar situation but I need to apply it to the whole sheet. I need to concatenate column B with column G in column J. I have posted similar questions on this site but yours has been the most helpful so far. Look at my previous posts and you will see what I mean. I would appreciate any help you can give on this effort. Thank you.

Sub BoldPartText()
Dim Part1Len, Part2Len, DividerLen As Integer
Dim Divider As String
Part1Len = Len(Range("b2")) + 1
Part2Len = Len(Range("g2"))
Divider = " - "
DividerLen = Len(Divider)
Range("j2").Clear
Range("j2") = Range("b2") & Divider & Range("g2")
With Range("j2").Characters(Start:=1, Length:=Part1Len).Font
.FontStyle = "Bold"
End With
End Sub

 

[vxl21]

Hi..
Is it possible to make this vba code as function?
I know that with macro in vba bold sm text is pble.

Thanks in adv.

 

[pgc01]

Hi

If you mean a udf, a vba function that you would call from a formula in a cell, the answer is no. All the characters in the result of a formula will have the same format.

 

[Coachmccann]

Here’s my scenario: I’m trying to draw from multiple cells to have one cell that produces a sentence that looks like this:
“Great job with your homework. You have scored 100%. Your next assignment is lesson 2.”

I’m struggling to bold the “lesson 2”. Here’s an example of the formula I’m using in cell A3: =CONCATENATE("Great job with your homework. You have scored "&text(A1,”0%”),". Your next assignment is "&(A2))

I’ve tried to manipulate your code listed below with little success:
Sub BoldPartText()
Dim Part1Len, Part2Len, DividerLen As Integer
Dim Divider As String
Part1Len = Len(Range("A1")) + 1
Part2Len = Len(Range("A2"))
Divider = ". "
DividerLen = Len(Divider)

Range("A3") = Range("A1") & Divider & Range("A2")
With Range("A3").Characters(Start:=Part1Len + DividerLen, Length:=Part2Len).Font
.FontStyle = "Bold"
End With
End Sub

Any suggestions on how to make this happen?

 

[cheshire]

Hi,

How do I manipulate this for Excel 2010?

I get the following error message:
The name that you entered is not valid.
Reasons for this can include:
- The name does not begin with a letter or an underscore
- the name contains a space or other invalid characters
- the name conflicts with an Excel buil-in name or the name of another object in the workbook.

Sub BoldPartText()
Dim Part1Len, Part2Len, DividerLen As Integer
Dim Divider As String
Part1Len = Len(Range("A1")) + 1
Part2Len = Len(Range("A2"))
Divider = ", "
DividerLen = Len(Divider)

Range("A3") = Range("A1") & Divider & Range("A2")
With Range("A3").Characters(Start:=Part1Len + DividerLen, Length:=Part2Len).Font
        .FontStyle = "Bold"
End With
End Sub