Excel VBA/Formula to search Column

Question

I need to search a column for 5-9 digit ID's that follow the term ^v_cusip= and are separated by commas. From first glance it seemed that the ID's were followed by ^v_sort_order so I tried:

=MID(A2,FIND("^v_cusip=",A2)+9,(FIND("^v_sort_order=",A2)-(FIND("^v_cusip=",A2)+9)))

However, what follows ^v_cusip= varies. Is there another formula or a VBA code I can use to search the rows? I cannot repeatedly use MID/FIND as there are thousands of rows with numerous variations of what can follow ^v_cusip=.

Thanks!

Have you looked at this? What you want is regex. http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Charlie, Jun 29 '15 at 15:25

score 0 · Answer 1 · answered Jun 29 '15 at 14:53

0

What does the rest of cell details look like? Is there only '=' sign in each row? If so, perhaps you could do a text to columns and use the '=' sign as the delimiter.

Once it's split should put the ID in a predictable column.

answered Jun 29 '15 at 14:53

Ryan Wildry

5,612
1
15
35

There is always a '^' at the end of the list of ID's but once again the cells contain many '^' – DragoSalaamii Jun 29 '15 at 15:04

score 0 · Accepted Answer · answered Jun 29 '15 at 15:30

0

=LEFT(RIGHT(A2;LEN(A2)-FIND("^v_cusip=";A2)-8);FIND("^";RIGHT(A2;LEN(A2)-FIND("^v_cusip=";A2)-8))-1)

This will show only the part between ^v_cusip= and the next ^ character. You can further split the string by comma.

answered Jun 29 '15 at 15:30

gipadm

543
7
17

Excel VBA/Formula to search Column

2 Answers2