Search code examples
google-sheetscell

Extracting specific information from a cell in Google sheets

I am just trying to get the ticker symbol from my cells, And my cells actually are in format of "stockexchange:ticker symbol", for example- NYSEARCA:VTI

How can I refer to the cell with my stock exchange and ticker symbol, such that I only extract the ticker symbol in google sheets?

Note: the ticker symbol is always going to be after the symbol colon :

Solution

  • Assuming you have the data in column A

    =index(if(len(A2:A), split(A2:A, ":"),),,2)

    or

    =ArrayFormula(IF(LEN(A2:A), REGEXEXTRACT(A2:A, "[^:]+$"),))

    Note: depending on your locale, you may have to change the comma's to semi colons.