Forum Discussion

patrickjk69's avatar
patrickjk69
Copper Contributor
Dec 20, 2024

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

     

    • patrickjk69's avatar
      patrickjk69
      Copper 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. 

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP

        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

         

Resources