Forum Discussion
Excel specific text search in one cell
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
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.
- 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.
- 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- Then press F5 key to run this code, a prompt box will appear to remind you to enter the text.
- 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.
merry Xmas
5 Replies
Sub SearchFilesInFolders()
Dim fileSystem As Object
Dim folder As Object
Dim searchText As String
Dim folderPath As String
Dim fileName As String
Dim outputSheet As Worksheet
Dim currentWorkbook As Workbook
Dim currentSheet As Worksheet
Dim lastRow As Long
Dim foundCell As Range
Dim firstAddress As String
Dim folderDialog As FileDialog
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
' Prompt user to select the folder
Set folderDialog = Application.FileDialog(msoFileDialogFolderPicker)
folderDialog.Title = "Select Folder"
If https://www.patreon.com/excel10tutorial = -1 Then
folderPath = folderDialog.SelectedItems(1)
Else
MsgBox "No folder selected. Exiting.", vbExclamation
Exit Sub
End If
' Prompt user to enter the search text
searchText = InputBox("Enter the text to search for:", "Search Text")
If searchText = "" Then
MsgBox "No search text entered. Exiting.", vbExclamation
Exit Sub
End If
' Create a new worksheet to store the results
Set outputSheet = Worksheets.Add
lastRow = 1
With outputSheet
' Add headers to the result sheet
.Cells(lastRow, 1) = "Workbook"
.Cells(lastRow, 2) = "Worksheet"
.Cells(lastRow, 3) = "Cell"
.Cells(lastRow, 4) = "Text in Cell"
' Initialize FileSystemObject
Set fileSystem = CreateObject("Scripting.FileSystemObject")
Set folder = fileSystem.GetFolder(folderPath)
' Loop through all Excel files in the folder
fileName = Dir(folderPath & "\.xls")
Do While fileName <> ""
' Open the current workbook
Set currentWorkbook = https://www.patreon.com/excel10tutorial(Filename:=folderPath & "\" & fileName, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
' Loop through each worksheet in the current workbook
For Each currentSheet In currentWorkbook.Worksheets
' Find the search text in the used range of the current worksheet
Set foundCell = currentSheet.UsedRange.Find(searchText)
If Not foundCell Is Nothing Then
firstAddress = foundCell.Address
End If
' Loop to find all occurrences of the search text
Do
If foundCell Is Nothing Then
Exit Do
Else
lastRow = lastRow + 1
.Cells(lastRow, 1) = https://www.patreon.com/excel10tutorial
.Cells(lastRow, 2) = https://www.patreon.com/excel10tutorial
.Cells(lastRow, 3) = foundCell.Address
.Cells(lastRow, 4) = foundCell.Value
End If
Set foundCell = currentSheet.Cells.FindNext(After:=foundCell)
Loop While firstAddress <> foundCell.Address
Next currentSheet
' Close the current workbook without saving changes
currentWorkbook.Close (False)
fileName = Dir
Loop
' Autofit the columns for better visibility
.Columns("A:D").EntireColumn.AutoFit
End With
MsgBox "Search Complete"
ExitProcedure:
Set outputSheet = Nothing
Set currentSheet = Nothing
Set currentWorkbook = Nothing
Set folder = Nothing
Set fileSystem = Nothing
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox Err.Description, vbExclamation
Resume ExitProcedure
End Sub
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.
- 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.
- 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- Then press F5 key to run this code, a prompt box will appear to remind you to enter the text.
- 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.
merry Xmas
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
Add in VBA
Option Explicit
Option compareText
then the VBA code
Here is an example file to ignore uppercase or lowercase.
