REPLACE PART OF CELL WITH FORMATTED TEXT

[Manju]

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]

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.

 

[Don Guillett]

If you want to do it manually>select cell>goto formula bar>select the text
to change>change it>touch enter key

 

[Manju]

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

 

[Harlan Grove]

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.

 

[Manju]

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,

 

[Gord Dibben]

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

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

 

[Harlan Grove]

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.

 

[Don Guillett]

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