I’ve spent the last 30 minutes trying to pull the tickers out of this column in Excel without any luck. I’m sure one of you gurus can do this in your sleep so any help is appreciated.
Here is a small sample of the data in Column A:
1-800 FLOWERS COM FLWS 1ST SOURCE CORP SRCE 1ST UNITED BANCORP INC FUBC 21ST CENTURY FOX INC FOXA 22ND CENTY GROUP INC XXII 2U INC TWOU 3D SYSTEMS CORP DDD 3M CO MMM 8X8 INC NEW EGHT A H BELO CORP AHC I would like to strip out the tickers which are at the end of the text string. Is this possible?
Didn’t think of that. After you trim you can use the find function to locate the position of spaces in the remaining text.
=find(" ",B1,1)
You’ll get errors for when no spaces are found, but you should get a position of 2 for the space when the ticker is either one or two characters long. For those records do another right function for a length of 2, and then trim again for tickers that are one character long.
Because of a variable number of spaces, I’m not sure there is a single formula answer here. You may need to go through several iterations of trimming and finding. I’m pretty sure a mildly competent person could write something in VBA that would solve this problem in 30 seconds. I am not that mildly competetent person.
This is my “power Excel” answer. Just highlight your data cells, click the Data menu -> Text to Columns. Choose “Delimited”, and set the divider to “Space”. This will tokenize your data into cells that are split by the spaces in the original fields.
where X changes based on how long the ticker is, or
RIGHT(A1,LEN(A1)-Y)
where Y is a cell that can identify where the last space in the string is.
The first would be easier if you have an excel function that reverses the order of the string (so Ford F becomes F droF), since then you can use search(" ",A1,1) to get the amount of text until the first space. I don’t think that’s built-in to excel, so the alternative (outside of creating a UDF) would be to identify where the last space is. I put
IFERROR(SEARCH(" “,$A1,1),”")
in B1
and then
IFERROR(SEARCH(" “,$A1,B1+1),”")
in C1 and then copy this over as far as appropriate.
At first I thought the same thing, but I wasn’t able to get it to work, since some of the names have two words “Pepsi Inc” and some have several words “First National Bank Corporation of New Jersey Inc.”
That’s so weird. Both mine and villnius’s suggestion work. You just have to retype the quotations. For some reason, the quotations are different on AF.
Hi! I’ve been a really long time lurker on this forum. I stumbled upon it after I was through with the exams, but needed some info on an excel or valuation problem I was having and I found it here, so I guess I can return the favor. Wanted to write this earlier, but I had to wait until my account was approved. Nonetheless, maybe this will help you or someone else. I believe the function in Excel VBA (not a big fan) would be: Function getTicker(str As String) Dim parts As Variant parts = VBA.Split(str) getTicker = parts(UBound(parts)) End Function @jmh530 Yeah, in python it would just be (and I got this from the top of my head): def getTicker(str): ticker = str.split(’ ')[-1] return ticker
