DEAR ALL,
HOW DO I CHANGE PART OF CELL (TEXT IN NATURE) WITH FORMATTED TEXT?
EXAMPLE

A1=TRR/SM/HK

I WANT IT TO APPEAR LIKE TRR/SM/HK WITH SM IN BOLD AND ITALICS WITH
YELLOW HIGHLIGHTING

I TRIED DOING NORMAL REPLACE WITH FORMAT BUT THE WHOLE CELL CONTENT
CHANGED INTO BOLD AND ITALICS WITH HIGHLIGHT

THANKS

Manju wrote...
....
I WANT IT TO APPEAR LIKE TRR/SM/HK WITH SM IN BOLD AND ITALICS WITH
YELLOW HIGHLIGHTING
....

Don't use all caps. It's considered shouting.

You'd need to select the SM in the cell contents, press [Ctrl]+1, then
use the formatting dialog.

If you want to do it manuallyselect cellgoto formula barselect the text
to changechange ittouch enter key

--
Don Guillett
SalesAid Software

"Manju" wrote in message
ps.com...
DEAR ALL,
HOW DO I CHANGE PART OF CELL (TEXT IN NATURE) WITH FORMATTED TEXT?
EXAMPLE

A1=TRR/SM/HK

I WANT IT TO APPEAR LIKE TRR/SM/HK WITH SM IN BOLD AND ITALICS WITH
YELLOW HIGHLIGHTING

I TRIED DOING NORMAL REPLACE WITH FORMAT BUT THE WHOLE CELL CONTENT
CHANGED INTO BOLD AND ITALICS WITH HIGHLIGHT

THANKS

Harlan Grove wrote:
Manju wrote...
...
I WANT IT TO APPEAR LIKE TRR/SM/HK WITH SM IN BOLD AND ITALICS WITH
YELLOW HIGHLIGHTING
...

Don't use all caps. It's considered shouting.

You'd need to select the SM in the cell contents, press [Ctrl]+1, then
use the formatting dialog.

Sorry for using all caps.
I meant to change a vast range of cells which has other persons
initials also along with mine (SM) in a single cell to know all the
places where my name is appearing.
If i need to select each cell individually and change the formatting it
doesn't serve my purpose. I hope you have a solution.

How do I attach a excel file if I want to show you the actual
spreadsheet with problem ?
Regards.

Manju

Manju wrote...
....
I meant to change a vast range of cells which has other persons
initials also along with mine (SM) in a single cell to know all the
places where my name is appearing.
If i need to select each cell individually and change the formatting it
doesn't serve my purpose. I hope you have a solution.

The only way to make batch formatting changes to SUBSTRINGS of cell
contents is by macro. If you're willing to use macros, and if the
initials you want to change are SM always appearing between the first
and second instance of /, and if you preselect the cells involved, you
could use the following macro.

Sub foo()
Const INITIALS As String = "hg" & "/"

Dim c As Range, s As String, n As Long, p As Long

If Not TypeOf Selection Is Range Then Exit Sub

n = Len(INITIALS)

For Each c In Selection
s = c.Text
p = InStr(1, s, "/") + 1

If p 1 And Mid(s, p, n) = INITIALS Then
With c.Characters(p, n - 1)
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 5 'blue - change as needed

End With

End If

Next c

End Sub

How do I attach a excel file if I want to show you the actual
spreadsheet with problem ?

Don't. I don't open files from people I don't know, and anyone else
with any sense would also refuse to open your file. Plain text
descriptions are almost always sufficient.

No sir, the person who types the MOM doesn't follow a standard to write
the Persons responsible column in any particluar order and I cannot
insist him doing so. I now manually see whereever my initials appear,
and highlight the whole row for my reference.
Sorry the macro didn't work for my data is not in an order.
thanks for trying. Is there something else, i can try however crude
that will be
regards,



Harlan Grove wrote:
Manju wrote...
...
I meant to change a vast range of cells which has other persons
initials also along with mine (SM) in a single cell to know all the
places where my name is appearing.
If i need to select each cell individually and change the formatting it
doesn't serve my purpose. I hope you have a solution.

The only way to make batch formatting changes to SUBSTRINGS of cell
contents is by macro. If you're willing to use macros, and if the
initials you want to change are SM always appearing between the first
and second instance of /, and if you preselect the cells involved, you
could use the following macro.

Sub foo()
Const INITIALS As String = "hg" & "/"

Dim c As Range, s As String, n As Long, p As Long

If Not TypeOf Selection Is Range Then Exit Sub

n = Len(INITIALS)

For Each c In Selection
s = c.Text
p = InStr(1, s, "/") + 1

If p 1 And Mid(s, p, n) = INITIALS Then
With c.Characters(p, n - 1)
.Font.Bold = True
.Font.Italic = True
.Font.ColorIndex = 5 'blue - change as needed

End With

End If

Next c

End Sub

How do I attach a excel file if I want to show you the actual
spreadsheet with problem ?

Don't. I don't open files from people I don't know, and anyone else
with any sense would also refuse to open your file. Plain text
descriptions are almost always sufficient.

This may come close.

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, "SM")
If start_str Then
With cell.Characters(start_str, 2).Font
.FontStyle = "Bold Italic"
.ColorIndex = 6
End With
End If
Next
End Sub


Gord Dibben MS Excel MVP

On 27 Dec 2006 12:41:50 -0800, "Manju" wrote:


Harlan Grove wrote:
Manju wrote...
...
I WANT IT TO APPEAR LIKE TRR/SM/HK WITH SM IN BOLD AND ITALICS WITH
YELLOW HIGHLIGHTING
...

Don't use all caps. It's considered shouting.

You'd need to select the SM in the cell contents, press [Ctrl]+1, then
use the formatting dialog.

Sorry for using all caps.
I meant to change a vast range of cells which has other persons
initials also along with mine (SM) in a single cell to know all the
places where my name is appearing.
If i need to select each cell individually and change the formatting it
doesn't serve my purpose. I hope you have a solution.

How do I attach a excel file if I want to show you the actual
spreadsheet with problem ?
Regards.

Manju

Gord Dibben wrote...
This may come close.
....

or not

start_str = InStr(cell.Value, "SM")
If start_str Then
....

This will format all instances of the substrings "SM", "Sm", "sM" and
"sm" under default settings, including substrings that are parts of
longer words, e.g., some other person with initials SMR would have the
"SM" formatted.

In order to automate this in a way that doesn't cause more problems
than it solves, the initials sought must appear as separate tokens,
i.e., with nonalphanumeric characters (or beginning or end of line)
delimiting them.

How about?
start_str = InStr(cell.Value, "SM")
start_str = InStr(cell.Value, "/SM/")
Then adjust the characters statement.

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, "/SM/")
If start_str Then
With cell.Characters(start_str+1, 2).Font
.FontStyle = "Bold Italic"
.ColorIndex = 6
End With
End If
Next
End Sub

--
Don Guillett
SalesAid Software

"Harlan Grove" wrote in message
ps.com...
Gord Dibben wrote...
This may come close.
...

or not

start_str = InStr(cell.Value, "SM")
If start_str Then
...

This will format all instances of the substrings "SM", "Sm", "sM" and
"sm" under default settings, including substrings that are parts of
longer words, e.g., some other person with initials SMR would have the
"SM" formatted.

In order to automate this in a way that doesn't cause more problems
than it solves, the initials sought must appear as separate tokens,
i.e., with nonalphanumeric characters (or beginning or end of line)
delimiting them.