Excel's TEXTBEFORE and TEXTSPLIT Functions

Thom Hartle – June 27, 2025

One important step when building Excel market dashboards is managing screen real estate. For example, in a recent post "CQG CME Markets Dashboards" a section displayed Corn 1-month Calendar spreads.

f1

The symbol "ZCES1?1" using wild cards is calling for the first Corn 1-Month Calendar spread. The long description shown above displays the legs (1*ZCEN25-1*ZCEU25).

=RTD("cqg.rtd", ,"ContractData","ZCES1?1", "longDescription")

Everything to the left of the legs is not necessary and is a waste of space.

This post "Excel 365 TEXTAFTER Function" provided a solution.

Excel 365 offers the TEXTAFTER function. This function makes this streamlining process 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"),",")

And returns:

(1*CLEQ25-1*CLEU25)

If you wanted to remove the parentheses, then the TEXTBEFORE function could be used:

=RIGHT(TEXTBEFORE(RTD("cqg.rtd", ,"ContractData","ZCES1?1", "longDescription"),")"),LEN(RTD("cqg.rtd", ,"ContractData","ZCES1?1", "longDescription"))-LEN(TEXTBEFORE(RTD("cqg.rtd", ,"ContractData","ZCES1?1", "longDescription"),"("))-2)

The function is finding the number of characters between the first and last parentheses and using the RIGHT function to remove the parentheses.

This function returns:

1*ZCEN25-1*ZCEU25

The TEXTAFTER function looked for the "," as the delimiter. The TEXTBEFORE function looked for the "(" and ")" as the delimiters.

Another solution is to use the TEXTSPLIT function. For example, the Soybean Crush spread long description, which is this symbol "F.US.SOMI01" returns:

Soybean Crush (Meal, Oil after Beans exp), (-10*ZSEX25+9*ZLEZ25+11*ZMEZ25)

Above, we have multiple parenthesis and commas. Using the TEXTSPLIT function looking for the comma:

=TEXTSPLIT(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"),",")

Returns an array:

f2

The INDEX function can be used to return the third output.

=INDEX(TEXTSPLIT(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"),"("),3)

f3

The INDEX Function can be modified to remove the parenthesis.

=LEFT(INDEX(TEXTSPLIT(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"),"("),3),LEN(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"))-LEN(TEXTBEFORE(RTD("cqg.rtd", ,"ContractData","F.US.SOMI01", "longDescription"),"(",-1))-2)

f4

Below is an image of the downloadable sample.

f5

The downloadable sample spreadsheet uses the detailed functions.

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

Downloads

Disclaimer

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.