Excel 365 TEXTAFTER Function

Microsoft® Excel 365 includes a Text function that makes pulling text out of a string much easier than before Excel 365 was released. To illustrate, we will review the steps before Excel 365 was released.

Excel's Right function will trim a text string from the right. For example, the RTD call for the Long Description:

=RTD("cqg.rtd", ,"ContractData","CLE", "LongDescription",, "T")


Crude Light (Globex), Feb 24

And, if you want your Excel dashboard to only display the month and the year for the futures contract to save space you can use the Right function set to six characters:

=RIGHT(RTD("cqg.rtd", ,"ContractData","CLE", "LongDescription",, "T"),6)


Feb 24

All good, however, if you changed the symbol to be a calendar spread, such as CLES1 the Long description returns:

Crude Light (Globex) Calendar Spread 1, Jan 24, Feb 24

But using the same Right function:

=RIGHT(RTD("cqg.rtd", ,"ContractData","CLES1", "LongDescription",, "T"),6)


Feb 24


Jan 24, Feb 24

The solution is to first determine the length of the text string, second find the location of the first comma in the string and for the Right function use the difference between the length and the location of the first comma:

The Length (LEN) function is:

=LEN(RTD("cqg.rtd", ,"ContractData", “CLES1”, "LongDescription",, "T"))



The FIND function looks for the location of ",":

=FIND(",",RTD("cqg.rtd", ,"ContractData",“CLES1”, "LongDescription",, "T"))



The difference between the two is 15.

Use the difference between the two functions to calculate the number of characters for the RIGHT function:

=RIGHT(RTD("cqg.rtd", ,"ContractData","CLES1", "LongDescription",, "T"),LEN(RTD("cqg.rtd", ,"ContractData", "CLES1", "LongDescription",, "T"))-FIND(",",RTD("cqg.rtd", ,"ContractData", "CLES1", "LongDescription",, "T")))


Jan 24, Feb 24

Excel 365 offers the TEXTAFTER function. This new function makes this process much easier. The function returns text that occurs after a given character or string, in this case it is ",":

=TEXTAFTER(RTD("cqg.rtd", ,"ContractData","CLES1", "LongDescription",, "T"),",")


Jan 24, Feb 24

There is a space after the delimiter which can be removed by using the TRIM function. Finally, if cell references for the RTD function are used for the symbols, and you enter a symbol such as SPY (the S&P 500 ETF) then the TEXTAFTER function will display an error as there is no “,” in the Long Description. IFERROR can be used to display the original Long Description.

=IFERROR(TRIM(TEXTAFTER(RTD("cqg.rtd", ,"ContractData",A2, "LongDescription",, "T"),",")),RTD("cqg.rtd", ,"ContractData",A2, "LongDescription",, "T"))

There is a downloadable sample spreadsheet at the bottom of the post.


Requirements: CQG Integrated Client or QTrader, and Excel 365 (locally installed, not in the Cloud) or more recent.



Trading and investment carry a high level of risk, and CQG, Inc. does not make any recommendations for buying or selling any financial instruments. We offer educational information on ways to use our sophisticated CQG trading tools, but it is up to our customers and other readers to make their own trading and investment decisions or to consult with a registered investment advisor. The opinions expressed here are solely those of the author and do not reflect the opinions of CQG, Inc. or its affiliates.