Referencing Question

[KyleD]

I have been working on table in excel for the purposes of analyzing stocks, and have run into an issue that should be fairly straight forward. I am trying to take a string of text and compare it to a stock symbol. If the symbol is found in the string I would like it to populate the first column of my table.

Table 1

JAZZ	179.73
DIS	110.12
BA	141.85
YHOO	43.31

<tbody>
</tbody>

Table 2

JAZZ	JAZZ150717C00160000 <tbody> </tbody>
	JAZZ150717C00195000 <tbody> </tbody>
	DIS150717C00060000 <tbody> </tbody>
	BA150717C00120000 <tbody> </tbody>

<tbody>
</tbody>

Basically, I want to populate column A of Table 2 with the stock symbol found as part of the string in Column B. I have tried various references, but haven't gotten it to work properly. Alternatively, if there is a way to simply have a formula in Column A remove all the numbers and the letter C from the string in Column B and leave the remaining letters that would work as well. Any help would be much appreciated! The difficulty I have found is the varying length of the stock symbol in Column B complicates the process of simply taking the first 4 characters in the Column to populate Column A.

Thanks for any help!

 

[JLGWhiz]

Formula?

=IF(B2>"",LEFT(B2, FIND("C", B2, 1) - 7),"")

Place this in column A of the first row of data, change the B2 above to the correct row number, then drag down as far as needed. If no data is in column B then column A will appear to be blank.

 

[JoeMo]

If the position of the letter C in the string can be variable this UDF will still extract the symbol. I've assumed the symbol is always followed by a number.

Excel Workbook
	A	B
1	JAZZ	JAZZ150717C00160000
2	JAZZ	JAZZ150717C00195000
3	DIS	DIS150717C00060000
4	BA	BA150717C00120000
Sheet3

Function ExtractTicker(S As String)
With CreateObject("VBScript.Regexp")
    .Global = True
    .ignorecase = True
    .Pattern = "[A-Z]+(?=\d)"
    If .test(S) Then
        ExtractTicker = .Execute(S)(0)
    Else
        ExtractTicker = CVErr(xlErrNA)
    End If
End With
End Function

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.
8. Enter the function like a worksheet function as in the example above for cell A1.