I have a list of securities in my dataset, however, the system that generates this report sometimes includes the exchange name in the security id. so every month I find a few fields in my data set that are Security ID & Exchange name concatenated into one field. What would be the best way to identify and remove? Would it be through the find and replace tool? Or would a formula like IF contains or something be better suited?
Best approach will depend on what the data looks like, but typically your best bet will regex for parsing text. Do you have some example data that you could post?
@CDIns is the SecurityID always a set length of characters? If the securityID is always , say, 9 characters long and then the Exchange name makes it greater than 9, you could do a simple function like Left(SecurityID, 9).
@Raj Hi thank you for the reply. Attached is the output and what I'd like to change the ID to. The IDs vary in length but appear to always be at the end of the string not in the middle. Any pointers would be appreciated. Thank you!
