AT()

AT() returns the position of a character string within a specific character string. You must specify which occurrence of the string to search for.

If you are only concerned with whether a string is present, use the simpler FIND() function. For more information, see FIND().

Use AT() to find the location of a string or testing for multiple occurrences of a string. Use AT() to look for a character string in a text field (such as a description, name or address).

Function Format

AT(N,C1,C2)

AT() searches for strings inside other strings and takes three arguments: N, C1 and C2.

The value returned is a location within searched C2 string. AT() can be interpreted as “at what position is the Nth occurrence of the string C1 in the searched C2 string?”.

If C1 is found N times in the searched C2 string, AT() returns the position of the first character of the Nth occurrence in the searched C2 string. If C1 does not occur N times in the searched C2 string, AT() returns zero value.

Note: AT() is case-sensitive, so “a” is not equal to “A.” If you are searching for values in mixed case data, you should consider converting the data to upper case with the UPPER() function.

Examples

AT(1, "ABC", "ABCDEFG")= 1

AT(2, "ABC", "ABCDEFGABCDEFG")= 8

AT(2, "A", "ABCDEFGH")= 0

AT(3, "ABC", "ABCDEFGABCDEFG")= 0

To extract every record in which the address is a P.O. Box, specify the condition:

AT(1,"P.O. BOX",UPPER(ADDRESS))>0

The UPPER() function is used so that the search is not case-sensitive. If the AT() expression returns a value greater than zero, then “P.O. BOX” was found in the Address field and the record is extracted.

To count the number of records in a file in which the last name is “Dixon”, specify the condition:

AT(1,"Dixon",LastName)>0

Or if the data is mixed case, specify the condition:

AT(1,"DIXON",UPPER(LastName))>0