Trying to find how to dynamically parse out text using delimiters.

[JBNTS]

Hi there,

I am looking to parse out text from a data string, example below:

AX_TEST_EXAMPLE_XYZ [13141-14141-14]

Using LEFT I can get the first two letters and using RIGHT I can get the final string between the [], I used MID to get the TEST text by using the FIND function. Now I am stuck, as I need the text between each "_".

Is there a way to define the first, second and third "_" in the MID formula?

Thanks

[MrShorty]

It sounds like you are somewhat familiar with the FIND() function, but perhaps you are unaware of the optional 3rd argument: https://support.office.com/en-us/art...3-d0d89b0a0628
After you find the position of the first _, finding the second one should be the same as finding the first, just telling the FIND(() function to start the 2nd search in the position after the 1st _.

On edit: At the risk of offending the Excel community, if you are not "married" to Excel for this, you could take your data to Google Sheets and use their built in =SPLIT() function to parse the text using _ as the delimiter. Such a spreadsheet would not be cross-compatible with Excel, but it might be easier than trying to nest together the mess of "MID(FIND(MID(...))))" functions that could be needed for this operation in Excel.

[JBNTS]

Hi,

Thanks for your reply.

This is really helpful, but where I am having difficulty is that the length of the text is different in each data string, so I cannot use a ubiquitous formula to find the start point of the second delimiter. Example below:

AX_TEST_EXAMPLE_XYZ [13141-14141-14]
TZ_TESTING_EXAMPLES_XYZ [13141-14141-14]

[JBNTS]

So I guess if I were to simplify the question, is there a formula that can say, "Find the text between the 2nd and 3rd delimiters"

[MrShorty]

I don't understand. It looks to me like it should work just fine. The total length of the string or the length of any substring should not impact the result.
(In B1) =FIND("_",$A1,1) should return 3
(In C1)=FIND("_",$A1,B1+1) should return 8
(In D1) copy of C1 should return 16
Copy B1:D1 down to B2:D2 and you should get 3, 11, and 20

A MID() function should then easily extract the text between any two of the delimiters.

[Tony Valko]

Try this...

Data Range

	A	B	C	D
1	AX_TEST_EXAMPLE_XYZ [13141-14141-14]	TEST	EXAMPLE	XYZ
2	TZ_TESTING_EXAMPLES_XYZ [13141-14141-14]	TESTING	EXAMPLES	XYZ
3				------

This formula entered in B1:

=TRIM(MID(SUBSTITUTE(LEFT($A1,FIND("[",$A1)-1),"_",REPT(" ",100)),COLUMNS($B1:B1)*100,100))

Copy across to D1 then down as needed.

[JBNTS]

That's it!

Thank you both very much for your help.

I am fast becoming a huge fan of this forum.

Until next time, thanks.

[Tony Valko]

You're welcome. Thanks for the feedback!

[JohnTopley]

Try

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"_"," "),"[",""),"]","")," ",REPT( " ",250)),(COLUMNS($A:A)-1)*250+1,250))

Text in A1, formula in B1 and drag across columns