extract multi-letter string bits from string depending on size and case

I have a bunch of cells on a sheet and need to find a specific one by a "key word" and then extract certain elements of it. The contents of the cell I'm looking for is similar to:

"The symbols are: ABC, DEF, ASD; more symbols: SDSF, ASD; also: UOI, DGF". With my keyword being "The symbols are".

If a cell containing this keyword is found, I'd like to extract only elements of it that have between 1 and 4 letters and are in all caps (the elements are stock symbols, fyi) and put it into an array held in VB memory that I can play with later. So, the exacted array would be: (ABC, DEF, ASD, SDSF, ASD, UOI, DGF).

Is this possible to do? Note that the cell contents are rather sporadic and are not nicely separated by comma or a space and the keyword is not always in the very front either (sometimes there's more than one, but I'm starting with one), that's why I have the 1 to 4 letters and all caps criteria to find what I need.

Thank you

Wow, that is a handful! Thank you. I'm actually looking to do this completely in VBA within a larger code I'm writing. I did some searching and looks like I can use the InStr function to find my keyword:

Please Login or Register  to view this content.

Could you show me how to modify what you have for use just within VBA? I gave it a try and got error after error.

Thanks so much.

Great! Are you able to make it so that the results are kept within a 1-dimensional array in VBA instead of being output to spread sheet?

Originally Posted by jindon

Do you understand what you are saying?

Please Login or Register  to view this content.

x should be 1 d array.

Lol. Of course. Thank you so much for your help.

You're welcome. I have not worked much with strings so takes a bit to comprehend.

Sometimes there is more than one line with different keywords like "The symbols are" or "here are the symbols" or "symbol list". Each line will contain a different set of symbols. Is it possible to add these other symbols to the array x? Basically the logic would be if a line contains one of the keywords, then save the symbols on that line to x, move on to next line, if it contains one of the keywords, add these to x, and so on.

Great! Thank you.