How to replace text with corresponding pictures in Excel?

In practical work, we often want to automatically display corresponding images in Excel based on the text in a particular cell (for example, displaying product images based on product names or showing corresponding icons based on statuses). Since Excel itself does not have a built-in "text-to-image" feature, some techniques need to be used to achieve this goal. Below, this article will detail two main solutions and their optimization methods.

Replace text with corresponding pictures by uisng VBA code

Replace text with corresponding pictures with Kutools for Excel

Replace text with corresponding pictures with VBA code

Normally, there is no easy and direct way for us to insert the relative pictures based on the text string in Excel, but, the following VBA code may help you to replace the text with its corresponding picture.

1. Press "Alt + F11" to display the "Microsoft Visual Basic for Applications" window.

2. In the window, click "Insert" > "Module" to show a new module window, then copy and paste the following VBA code into the module window.

VBA code: replace text with corresponding pictures

Sub ReplaceTextWithPictures()
'Updateby Extendoffice
    Dim ws As Worksheet
    Dim selectedRange As Range
    Dim cell As Range
    Dim pic As Picture
    Dim picPath As String
    Dim foundPic As Boolean
    Dim fileExtensions As Variant
    Dim ext As Variant
    Const imageFolderPath As String = "C:\Users\skyyang\Desktop\Animal\"
    fileExtensions = Array("png", "jpg", "jpeg", "bmp", "gif")
    On Error Resume Next
    Set selectedRange = Application.InputBox("Select the data range", "KutoolsforExcel", Type:=8)
    On Error GoTo 0
    If selectedRange Is Nothing Then
        MsgBox "No valid cell range selected, operation cancelled.", vbExclamation
        Exit Sub
    End If
    Set ws = selectedRange.Worksheet
    For Each cell In selectedRange
        If Not IsEmpty(cell.Value) Then
            foundPic = False
            For Each ext In fileExtensions
                picPath = imageFolderPath & cell.Value & "." & ext
                If Dir(picPath) <> "" Then
                    foundPic = True
                    Exit For
                End If
            Next ext
            If foundPic Then
                For Each pic In ws.Pictures
                    If pic.TopLeftCell.Address = cell.Address Then pic.Delete
                Next pic
                Set pic = ws.Pictures.Insert(picPath)
                With pic
                    .Top = cell.Top
                    .Left = cell.Left
                    .Width = cell.Width
                    .Height = cell.Height
                End With
            End If
        End If
    Next cell
End Sub

3. Then press F5 key to run this code, and select the data range that you want to insert the corresponding pictures in the popped out dialog, see screenshot:

4. And then click OK button, all the corresponding images will replace the original text, see screenshot:

Replace text with corresponding pictures with Kutools for Excel

If you're not familiar with or don't want to use VBA code, you can try Kutools for Excel. It offers a powerful and efficient solution that allows you to easily replace text with corresponding images in just a few clicks. With Kutools, you can match specific text with preset images directly, without the need to write any code. Additionally, you can adjust the size of the images according to your needs, providing more flexibility in how they fit within your Excel sheet.

After installing Kutools for Excel, please do as this:

1. Click "Kutools Plus" > "Import & Export" > "Match Import Pictures", see screenshot:

2. In the "Match Import Pictures" dialog box, please set the options as this:

• From the "Match range" textbox, please click to select the cell range you want to match with the pictures;
• Then, click "Add" button to select the picture files you want to import;
• Specify the picture size you want to insert from the Import size drop down (Here, you can choose "match the image size", "match the cell size" or "specify the image size" as you need.);
• Select the import order for your images;
• Finally, click "Import" button.
  

3. And then, choose one cell to locate your relative images from the popped out "Match Import Pictures" dialog (you can also select the original cell of the text), see screenshot:

4. And then click OK button, all the pictures which match with the text have been inserted into the worksheet, see screenshot:

In Excel, using VBA code or the Kutools for Excel tool to replace text with corresponding images can significantly enhance work efficiency and data visualization. Whether through writing VBA scripts or using the Kutools plugin, both methods help users quickly achieve text-to-image replacement, catering to different work needs. Choosing the right method can make data processing more efficient and intuitive, thereby improving overall work quality. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.