Pull cell formatting into Concatenate or "&" function?

[Cara]

Hi~

Is it possible to format a column (bold type for example)
and then, when that cell is referenced in a concatenate
or "&" function, the formatting remains on just that data?

E.G. In the following data, the first row is my header
row. The next two rows are the data.

Program Name City, ST Combined
Annual Conf. Reno, NV Annual Conf. - Reno, NV
Regional Trng. Boston, MA Regional Trng - Boston, MA

What I would like to do is BOLD the first column, and then
in column 3 "Combined"(which I achieved with the following
formula =A2&" - "&B2) I would like the Bold format to
remain on the program name, with the rest of the info
remaining as it was, no formatting.

Is there a way of doing this?

Thanks,
Cara

 

[Frank Kabel]

Hi
no this ios not possible. Formulas can only return values, never
formats

 

[Cara]

Hi Frank,

Thanks for the response, at least now I don't have to
spend hours playing around with something that isn't even
possible.

Thanks!

 

[Ken]

If you wanted to do it using a macro, the following code
should acccomplish what you want:

Dim RowCount As Double
Dim ILoop As Double
Dim FindDash As Integer
Dim Pos As Integer

Sub BoldRows()
RowCount = Cells(Rows.Count, 3).End(xlUp).Row
For ILoop = 1 To RowCount
Pos = InStr(1, Cells(ILoop, 3), "-")
Cells(ILoop, 3).Characters(Start:=1, Length:=Pos -
1).Font.FontStyle = "Bold"
Next ILoop
End Sub


Assumes that data is in column C. Will bold everything to
the left of the first dash (-).

 

[Gord Dibben]

Cara

It is possible after the fact using VBA.

If your program name is always followed by a <space>hyphen<space> as in your
examples you could use this code to bold up to the hyphen.

Can't attribute the code 'cause I don't remember who posted it. TO or JW, I
believe. Maybe CP.

First, copy and paste special>Values to get rid of your formulas.

Sub BoldFirstWord()
Dim rng As Range, Cell As Range
Dim iloc As Long
Set rng = Range(Cells(1, 3), _
Cells(1, 3).End(xlDown))
rng.Font.Bold = False
For Each Cell In rng
iloc = InStr(Cell.Formula, " - ")
If iloc = 0 Then
If Len(Trim(Cell.Formula)) > 1 Then
Cell.Font.Bold = True
End If
Else
Cell.Formula = (Left(Cell.Formula, iloc - 1)) & _
Right(Cell.Formula, Len(Cell.Formula) - iloc + 1)
Cell.Characters(Start:=1, Length:=iloc - 1). _
Font.FontStyle = "Bold"
End If
Next
End Sub

As written it operates on Column C only.

Gord Dibben Excel MVP

 

[Cara]

Thanks Ken and Gord for both of your suggestions.
Unfortunately, I don't know VBA or macros very well, and
was completely lost when I tried to stumble my way
through. For the amount of data I need formatted, I think
I'll just do it manually. Again, thanks for the help!!
~Cara