Apply formatting to only a portion of a cell's contents

User avatar
Samantha
3StarLounger
Posts: 363
Joined: 24 Jan 2010, 07:19
Location: Spokane, WA

Apply formatting to only a portion of a cell's contents

Post by Samantha »

I have a 150 page worksheet that contains the information on clients for which our law firm has done estate planning dating back to 1952. A few years ago, I migrated dozens of Word file indices to one Excel master index, and I am now trying to clean up that 'new' document.

As you might expect, many the clients for whom we have prepared Last Will and Testaments are now deceased, and that fact needs to be noted as a parenthetical (Dec'd) next to their name in the index, and I want to bold and italicize that notation, as well as have it appear in a slightly smaller font size.

I know that direct formatting can be applied to a portion of a cell's contents, simply by selecting the relevant portion and manually changing the formating, but I'd like to be able to do a global search and replace to find the string (Dec'd) and apply the formatting to that portion of the cell contents only. Unfortunately, the results of the Search & Replace affected the entire contents of the cells. Am I missing a setting in the search & replace dialogue to have only the "(Dec'd)" portion of the cell affected by the formatting change?
Samantha

User avatar
StuartR
Administrator
Posts: 12650
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Apply formatting to only a portion of a cell's contents

Post by StuartR »

You haven't missed anything. This is a very annoying feature of Excel.
StuartR


User avatar
Samantha
3StarLounger
Posts: 363
Joined: 24 Jan 2010, 07:19
Location: Spokane, WA

Re: Apply formatting to only a portion of a cell's contents

Post by Samantha »

StuartR wrote:You haven't missed anything. This is a very annoying feature of Excel.
:censored: Thanks for the quick confirmation Stuart, even though it's not the answer I was hoping for.
Samantha

User avatar
StuartR
Administrator
Posts: 12650
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Apply formatting to only a portion of a cell's contents

Post by StuartR »

If you have to do this often, or the spreadsheet is big enough to justify the effort, then you could write a Macro to do this.
StuartR


User avatar
mbarron
2StarLounger
Posts: 112
Joined: 25 Jan 2010, 20:19

Re: Apply formatting to only a portion of a cell's contents

Post by mbarron »

This macro checks all entries in column one and if it finds (Dec'd) in the cell, it changes the font to bold, italic and font size of 8.

Code: Select all

Sub deceased()
Dim i As Long, strD As String, iDec As Integer
i = 1
Do Until Cells(i, 1) = ""
If InStr(Cells(i, 1), "(Dec'd)") > 1 Then
iDec = InStr(Cells(i, 1), "(Dec'd)")

With Cells(i, 1).Characters(Start:=iDec, Length:=7).Font
        .Bold = True
        .Italic = True
        .Size = 8
End With


End If
i = i + 1
Loop

End Sub

User avatar
Samantha
3StarLounger
Posts: 363
Joined: 24 Jan 2010, 07:19
Location: Spokane, WA

Re: Apply formatting to only a portion of a cell's contents

Post by Samantha »

mbarron wrote:This macro checks all entries in column one and if it finds (Dec'd) in the cell, it changes the font to bold, italic and font size of 8.

Code: Select all

Sub deceased()
Dim i As Long, strD As String, iDec As Integer
i = 1
Do Until Cells(i, 1) = ""
If InStr(Cells(i, 1), "(Dec'd)") > 1 Then
iDec = InStr(Cells(i, 1), "(Dec'd)")

With Cells(i, 1).Characters(Start:=iDec, Length:=7).Font
        .Bold = True
        .Italic = True
        .Size = 8
End With


End If
i = i + 1
Loop

End Sub
That worked wonderfully. Thank you sooo much! :kiss:

Sorry it's taken so long to report back -- this is one of those projects that I only attend to when nothing else is on my desk, which rarely happens. But I couldn't let your assistance go unappreciated any longer, so I added your macro to my spreadsheet before closing up shop for the day, and it ran beautifully.
Samantha