Format part of text in concatenated field

[jday]

I have a formula in cell B2 that concatenates a combination of text &
contents from other cells in the worksheet. In the table below, cell C2
represents the color, cell D2 is the type of item, and cell E2 is the
quantity in inventory. For example:

C2 D2 E2
---- ---- ----
Blue Rug 3

The concatenation formula in cell B2 looks like this:

=C2&" "&D2&" - Qty ("&E2&")"

So that the result in B2 looks like this: Blue Rug - Qty (3)

What I would like is to format the "Qty (3)" portion of the text result to
be bold, or a different color, or italicized, or somehow highlighted to make
it stand out from the rest of the text in that cell. Is there a special
format code that I can insert right before each of the three components that
make up the " - Qty (3)" part of the text to make that happen?

 

[Mike H]

Hi,

A cell which contains text can have a format applied to part of that text,
but that can't be done if the text contains a formula, rather than just
text.

Mike

 

[ShaneDevenshire]

Hi,

This can be done using a picture but that's a really obtuse approach, if you
want it please post back and I will step you through the process when I get
home this evening.

 

[Ron Rosenfeld]

I have a formula in cell B2 that concatenates a combination of text &
contents from other cells in the worksheet. In the table below, cell C2
represents the color, cell D2 is the type of item, and cell E2 is the
quantity in inventory. For example:

C2 D2 E2
---- ---- ----
Blue Rug 3

The concatenation formula in cell B2 looks like this:

=C2&" "&D2&" - Qty ("&E2&")"

So that the result in B2 looks like this: Blue Rug - Qty (3)

What I would like is to format the "Qty (3)" portion of the text result to
be bold, or a different color, or italicized, or somehow highlighted to make
it stand out from the rest of the text in that cell. Is there a special
format code that I can insert right before each of the three components that
make up the " - Qty (3)" part of the text to make that happen?

That cannot be done with a formula in the cell.

However, you could use a macro to accomplish the concatenation; write the
result to the cell as a text string, and differentially format the Qty (3)
portion.

If the entries in C, D and e are entered manually as text, then something like
below might work for you:

right click on the sheet tab
select View Code
Paste the code below into the window that opens.

Make sure the range for data entry is the correct size.

================================================
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Dim sRes As String
Dim lQtyStart As Long, lQtyLen As Long
Application.EnableEvents = False
Set r = Range("C2:E100") 'set to data entry area

If Not Intersect(Target, r) Is Nothing Then
For Each c In Target
If Not Intersect(c, r) Is Nothing Then
If Application.WorksheetFunction.CountA _
(Range(Cells(c.Row, 3), Cells(c.Row, 5))) = 3 Then
sRes = Cells(c.Row, 3).Text & _
" " & Cells(c.Row, 4).Text & _
" - Qty (" & Cells(c.Row, 5).Text _
& ")"
lQtyStart = InStrRev(sRes, "(") + 1
lQtyLen = InStrRev(sRes, ")") - lQtyStart
With Cells(c.Row, 2)
.Value = sRes
.Characters(lQtyStart, lQtyLen).Font.Bold = True
.Characters(lQtyStart, lQtyLen).Font.Color = vbGreen
End With
Else
Cells(c.Row, 2).Value = ""
End If
End If
Next c
End If
Application.EnableEvents = True
End Sub
========================================

--ron

 

[Gord Dibben]

You cannot format parts of a formula.

Either manually copy and paste as values then select the portion to
highlight or use VBA which will do the same thing.

Sub Bold_String()
Dim rng As Range
Dim Cell As Range
Dim start_str As Integer
Set rng = Selection
For Each Cell In rng
start_str = InStr(Cell.Value, "Q")
If start_str Then
Cell.Value = Cell.Value
With Cell.Characters(start_str, Len(Cell)).Font
.Bold = True
.ColorIndex = 3
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP