formatting selected characters or numbers in each cell within a rangeof cells

[Colleen]

I am sending this to several Excel newsgroups, because I am not sure
which one if the most appropriate. I apologize in advance for the
multiple postings. I have MS Excel 2000 (version 9.0.3821 SR-1). How
can I format only a selected character or number within each cell in a
specific range of cells? For instance if I have a cell containing
“1234234”, how can I format it so that only the 2’s are bold or are
colored? Obviously, I have a range of cells, and I want to format all
the 2’s occurring in any of the cells as either bold or colored.
Conditional formatting does not seem to allow me to do this, and I have
been unable to find a formula that will accomplish this. Can someone help?
Thank you.
Colleen

 

[Ian]

I don't think this is possible with a number. You can't even do this
manually as a change to any digit within the number will affect the whole
cell. If the cell contents are actually text, it may be possible.

 

[Ron Rosenfeld]

I am sending this to several Excel newsgroups, because I am not sure
which one if the most appropriate. I apologize in advance for the
multiple postings. I have MS Excel 2000 (version 9.0.3821 SR-1). How
can I format only a selected character or number within each cell in a
specific range of cells? For instance if I have a cell containing
“1234234”, how can I format it so that only the 2’s are bold or are
colored? Obviously, I have a range of cells, and I want to format all
the 2’s occurring in any of the cells as either bold or colored.
Conditional formatting does not seem to allow me to do this, and I have
been unable to find a formula that will accomplish this. Can someone help?
Thank you.
Colleen

You could use a VBA macro to convert the number to a string, and then bold the
appropriate characters.

Here is an example:

=============================
Sub BoldTwos()
Dim c As Range
Dim s As Long
Const Twos As String = "2"

For Each c In Selection
s = 1
With c
.NumberFormat = "@"
.Value = CStr(.Value)
Do While InStr(s, c.Text, Twos) > 0
s = InStr(s, c.Text, Twos)
.Characters(s, 1).Font.Bold = True
.Characters(s, 1).Font.Color = vbRed
s = s + 1
Loop
End With
Next c

End Sub
==============================

To enter this, <alt><F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code above into the window that opens.

To use it, select a range of cells; <alt><F8> opens the Macro dialog box.
Select and Run BoldTwos.

This can be set up in a number of ways, depending on your specific
requirements.
--ron