Excel specific text search in one cell

[gijsvancuijk]

Hello Excel Profs,

i am searching for a way how to search in an excel sheet for a specific text AND mark/highlight this part. With the normal search function the cell is selected but not the specific text. Tried several functions (vlookup, xlookup, hlookup, match, xmatch, conditional fomat etc) but unfortunately without the succes. Can somebody help me with this?

many thanks in advance

[#2 · Accepted solution]

@gijsvancuijk 

Highlight one or more specific texts in multiple cells with VBA code

For example, I have a series of text strings and now I want to highlight the specific text.

To highlight only part of the text in a cell, the following VBA code might help you.

1. Select the cells where you want to highlight the specific text, and hold the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code into the Module window.

VBA code: Select a part of text in a cell:

Sub HighlightStrings()
Application.ScreenUpdating = False
Dim Rng As Range
Dim cFnd As String
Dim xTmp As String
Dim x As Long
Dim m As Long
Dim y As Long
cFnd = InputBox("Enter the text string to highlight")
y = Len(cFnd)
For Each Rng In Selection
  With Rng
    m = UBound(Split(Rng.Value, cFnd))
    If m > 0 Then
      xTmp = ""
      For x = 0 To m - 1
        xTmp = xTmp & Split(Rng.Value, cFnd)(x)
        .Characters(Start:=Len(xTmp) + 1, Length:=y).Font.ColorIndex = 3
        xTmp = xTmp & cFnd
      Next
    End If
  End With
Next Rng
Application.ScreenUpdating = True
End Sub

1. Then press F5 key to run this code, a prompt box will appear to remind you to enter the text.
2. And then click OK button, all the text you specified has only been highlighted in the cells.

Hope I could help you with this information and link.

NikolinoDE

merry Xmas

[#3]

@NikolinoDE 

Hello NikolinoDE,

many many thanks for your VBA script, it works perfectly. One small remark/question: the search is case sensitive and i want a case insensitive search. Of course i tried to fix it by myself but unfortunately without succes. It should be GREAT if you have a solution for this.

Already again many thanks for the script.

regards gijs

[#4]

@gijsvancuijk 

Add in VBA

Option Explicit

Option compareText

then the VBA code

Here is an example file to ignore uppercase or lowercase.

[#5]

perfect, thankssssssssssssss