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:
我需要在列中搜索@ v_cusip =后面的5-9位ID,并用逗号分隔。乍一看似乎ID后跟^ v_sort_order所以我试过:
=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=.
但是,^ v_cusip =后面的内容会有所不同。是否有其他公式或VBA代码可用于搜索行?我无法重复使用MID / FIND,因为有数千行,其中包含许多可以跟随^ v_cusip =的变化。
Thanks!
2 个解决方案
#1
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.
这将仅显示^ v_cusip =和下一个^字符之间的部分。您可以使用逗号进一步拆分字符串。
#2
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.
一旦拆分,应将ID放入可预测的列中。
#1
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.
这将仅显示^ v_cusip =和下一个^字符之间的部分。您可以使用逗号进一步拆分字符串。
#2
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.
一旦拆分,应将ID放入可预测的列中。