Jo has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Excel VBA Advanced course
Functions
Is there a code for finding a word or phrase in a string of text? In an excel formula you could use "*word required*", would this work in a VBA function?
RE: functions
Hi Jo,
Thank you for the forum question and thank for the nice feedback. I am happy that the course was useful.
We have a VBA function which can find a word or a phrase in a text string.
The InStr function.
See syntax and explanation below.
Syntax of InStr Function
InStr( [Start], String1, String2, [Compare] )
[Start] – (optional argument) this is an integer value that tells the InStr function the starting position from which it should start looking. For example, if I want the search to start from the beginning, I will enter the value as 1. If I want it to begin with the third character onwards, I will use 3. If omitted, the default value of 1 is taken.
String1 – This is the main string (or the parent string) in which you want to search. For example, if you’re looking for the position of x in Excel, String 1 would be “Excel”.
String2 – This is the substring that you are searching for. For example, if you’re looking for the position of x in Excel, String2 would be x.
[Compare] – (optional argument) You can specify one the following three values for [compare] argument:
vbBinaryCompare – This would do a character by character comparison. For example, if you’re looking for ‘x’ in ‘Excel’, it will return 2, but if you’re looking for ‘X’ in ‘Excel’, it will return 0 as X is in upper case. You can also use 0 instead of
vbBinaryCompare. If the [Compare] argument is omitted, this is the taken as default.
vbTextCompare – This would do a textual comparison. For example, if you look for ‘x’ or ‘X’ in Excel, it would return 2 in both the cases. This argument ignores the letter case. You can also use 1 instead of vbTextCompare.
vbDatabaseCompare – This is used for Microsoft Access only. It uses the information in the database to perform the comparison. You can also use 2 instead of vbDatabaseCompare.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
