My data contains a sequence of characters embedded in a larger text string. The sequence of characters is always the same, but the starting position of the characters differs between records. What is an easy way to determine the starting position in each record?
InStr function is useful in this situation. It determines the starting position of a string within a larger string. Since the string being sought is already known, determining its length is not an issue.
Figure 5-1 shows a table with records. Within each record is the substring 7XR3G. The positioning of this substring is not consistent across records.
Figure 5-1. Each record contains the same substring
You can use a query to determine the substring's start position in each record. You'll need to build a temporary field as an expression with the
InStr function, as shown in Figure 5-2.
Figure 5-2. Using InStr in a query
Figure 5-3 shows the result of running the query.
InStr returns the starting position for a given substring. But what if the substring occurs more than once in the larger string? You may need to be able to determine where each occurrence begins, not just the first occurrence.
A little VBA code helps out in this situation. ...