CODEWITHSUNDEEP
excelexcel-formula
cpburke94
cpburke94

Reputation: 1297

Excel formula for separating certain strings when it has a variety of text

I have a bunch of data where I'm looking to pull all "sample1:" and any letters that come immediately after it. I'm not sure if the best way is to separate all of the sample1's from the rest of the strings in the cells or what. But basically, "sample1:" could have multiple letters following it, and it could come at the beginning, middle or end of the string. I've attached a screenshot of the data format - for example, I want to pull everything that comes after sample1: and before the ; (ex. sample1:A,B,C;sample3:20 would only pull "sample1:A,B,C")

enter image description here

Upvotes: 0

Views: 89

Answers (1)

JvdV
JvdV

Reputation: 75960

If one has Excel O365, you could use LET(), e.g:

=LET(X,SEARCH("sample1:",A2),Y,SEARCH(";",A2&";",X),MID(A2,X+8,Y-X-8))

In case of Excel 2013 or higher, you could use FILTERXML(), e.g:

=FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,":",";"),";","</s><s>")&"</s></t>","//s[preceding::*[1]='sample1']")

In (AFAIK) all versions of Excel, you could keep using SEARCH():

=MID(A2,SEARCH("sample1:",A2)+8,SEARCH(";",A2&";",SEARCH("sample1:",A2))-SEARCH("sample1:",A2)-8)

enter image description here

If you need to keep "sample1" in there:

=LET(X,SEARCH("sample1:",A2),Y,SEARCH(";",A2&";",X),MID(A2,X,Y-X))

Or:

=FILTERXML("<t><s>"&SUBSTITUTE(A2,";","</s><s>")&"</s></t>","//s[starts-with(.,'sample1:')]")

Or:

=MID(A2,SEARCH("sample1:",A2),SEARCH(";",A2&";",SEARCH("sample1:",A2))-SEARCH("sample1:",A2))

Upvotes: 1

Related Questions