Forum Discussion
patrickjk69
Dec 20, 2024Copper Contributor
Find and replace with replacements in italics
Hi: I've created a pretty large spreadsheet listing a whole bunch of books in a library. Looks great, but there are a couple of issues I can't seem to resolve. The entries need the imprint to be in italics, thus:
ORWELL, (George). - Collected Essays and Journalism. Vol. 1. London: Penguin Books, 1973.
In most cases, I did the italicization as I made the entry, but I got lazy and missed a bunch. So what I need to do to do a search and replace operation in which just the imprint is italicized in the replace operation. I tried to do it, but the whole entry became italics not just the words I entered in the Find & Replace boxes. Is there a literal option I'm not seeing?
You need a VBA macro for this in Excel.
Sub Italicize() Const strText = "London: Penguin Books" Dim rng As Range Dim adr As String Dim pos As Long Set rng = Selection.Find(What:=strText, LookAt:=xlPart) If Not rng Is Nothing Then Application.ScreenUpdating = False adr = rng.Address Do pos = InStr(rng.Value, strText) rng.Characters(Start:=pos, Length:=Len(strText)).Font.Italic = True Set rng = Selection.Find(What:=strText, After:=rng, LookAt:=xlPart) If rng Is Nothing Then Exit Do Loop Until rng.Address = adr Application.ScreenUpdating = True End If End Sub
How can we determine exactly which part of the cell contents should be italicized?
- patrickjk69Copper Contributor
As described in my original post. The cell contains:
ORWELL, (George). - Collected Essays and Journalism. Vol. 1. London: Penguin Books, 1973.
The words 'London: Penguin Books' need to be in italics. There are many cells in the spreadsheet, each containing an entry for a book, as described above. Many of the books were published at 'London: Penguin Books' and those three words [and the colon of course] need italics.
Using the Find and Replace utility, I enter 'London: Penguin Books' [without the single quotes], and set it it up so that the specified text string is changed to italics. After the operation is complete the entire contents of each cell containing that string is italicized, not just the three words. Easy to do in Word, but apparently rather too sophisticated in Excel.
You need a VBA macro for this in Excel.
Sub Italicize() Const strText = "London: Penguin Books" Dim rng As Range Dim adr As String Dim pos As Long Set rng = Selection.Find(What:=strText, LookAt:=xlPart) If Not rng Is Nothing Then Application.ScreenUpdating = False adr = rng.Address Do pos = InStr(rng.Value, strText) rng.Characters(Start:=pos, Length:=Len(strText)).Font.Italic = True Set rng = Selection.Find(What:=strText, After:=rng, LookAt:=xlPart) If rng Is Nothing Then Exit Do Loop Until rng.Address = adr Application.ScreenUpdating = True End If End Sub