Index() searches a string to find the starting position of
the nth occurrence of a provided substring. An optional third argument provides the value of n, which is 1 if omitted. A negative value searches from the end of the string. The positions in the string are numbered left to right from
1 and up.
Syntax:
Index(text, substring[, count])
Return data type: integer
Arguments
Argument
Description
text
The original string.
substring
A string of characters to search for in text.
Information noteIf the substring does not exist in the text, Index returns 0.
count
Defines which occurrence of substring to search for. For example, a value of 2 searches for the second occurrence. A negative value starts from the end of the string.
Example: Chart expressions
Example
Result
Index( 'abcdefg', 'cd' )
Returns 3
Index(
'abcdabcd', 'b', 2 )
Returns 6 (the second occurrence of b)
Index( 'abcdabcd', 'b',-2 )
Returns 2 (the second occurrence of b starting from the end)
Left( Date, Index( Date,'-') -1 ) where Date = 1997-07-14
Returns 1997
Mid( Date, Index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14
Returns 07
Index( 'abc', 'x' )
Returns 0 (x does not exist in the string abc)
Index( 'abc', 'a', 2 )
Returns 0 (there is no 2nd occurrence of a)
Example - Index fundamentals
Overview
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
Load the data and open a sheet. Create a new table and add this field as a dimension:
InputString
Create the following calculated dimensions:
=Index(InputString, 'cd')
=Index(InputString, 'e')
=Index(InputString, 'b', -1)
Results table
InputString
Index(InputString, 'cd')
Index(InputString, 'e')
Index(InputString, 'b', -1)
abcdabcd
3
0
6
abcdefg
3
5
2
The first row with the string abcdabcd returns:
3—the first occurrence of cd
0—because e is not found in the string
6—the first occurrence of b starting from the end of the string
The second row with the string abcdefg returns:
3—the first occurrence of cd
5—the first occurrence of e
2—the first occurrence of b starting from the end of the string
Information noteA negative count parameter scans the string in reverse. However, the position returned is always denoted from left to right, even if scanned in reverse.
The following code shows how to use the function in a load script.
Example:
Load
*,
index(String, 'cd') as Index_CD, // returns 3 in Index_CD
index(String, 'b') as Index_B, // returns 2 in Index_B
index(String, 'b', -1) as Index_B2; // returns 2 or 6 in Index_B2
Load * inline [
String
abcdefg
abcdabcd ];
Results table
String
Index_CD
Index_B
Index_B2
abcdefg
3
2
2
abcdabcd
3
2
6
Example - Index scenario
Overview
A customer support center has a dataset that contains subject lines from customer email messages. The support center wants to categorize these emails based on the keywords that appear in the subject line, for example, Refund, Complaint, and Technical Issue. The subject lines are stored in a field in the dataset called EmailSubject.
Open the Data load editor and add the load script below to a new tab.
The load script contains:
A dataset which is loaded into a data table called Example.
One field in the data table called EmailSubject.
Load script
Example:
Load * inline [
EmailSubject
Request for a Refund - Order 12345
Technical Issue with Product XYZ
Complaint about delivery service
Follow-up on Refund Request
Technical Issue not resolved
];
Results
Load the data and open a sheet. Create a new table and add this field as a dimension:
EmailSubject
Create the following calculated dimension:
=If(Index(EmailSubject, 'Refund') > 0, 'Refund', 'Other'), to calculate which email subject text relate to refunds.
The output of the Index function has successfully identified all subject lines which contain the string Refund. This is an example of how you can analyze and categorize data using nested functions and deliver value to users.
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!
When you visit any website, it may store or retrieve information on your browser, mostly in the form of cookies. The information does not usually directly identify you, but it makes the site work as you expect it to and can give you a more personalized web experience. Because we respect your right to privacy, you can choose not to allow some types of cookies by clicking on the different category headings to find out more and change your settings. However, blocking some types of cookies may impact your experience of the site and the services we are able to offer.
Privacy & Cookie Notice
Manage Consent Preferences
Strictly Necessary Cookies
Always Active
These cookies are necessary for the website to function and cannot be switched off in our systems. They are usually only set in response to actions made by you which amount to a request for services, such as setting your privacy preferences, logging in or filling in forms. You can set your browser to block or alert you about these cookies, but some parts of the site will not then work.
Functional Cookies
These cookies enable the website to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we have added to our pages. If you do not allow these cookies, then some or all of these services may not function properly. These cookies do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device.
Performance Cookies
These cookies allow us to count visits and traffic sources so we can measure and improve the performance of our site and make it easier to navigate. For example, they help us to know which pages are the most and least popular and see how visitors move around the site. When analyzing this data it is typically done on an aggregated (anonymous) basis.
Advertising Cookies
These cookies may be set through our site by our advertising partners to build a profile of your interests and show you relevant advertisements on other sites. They do not typically store personal information enabling us to identify you, but are based on uniquely identifying your browser and internet device. If you do not allow these cookies, you will experience less relevant advertising.