How to get a value in an Excell cell and place it in another column?

Question

I have a big Excel file with a lot of series and episodes, like this

I need to get every episode value (e.g. e01, E02 etc.) and place it in the right column or leave it in the current one.

Some of like don't have episodes, so it must be omited.

What a formula/script do I need to do it?

Post data with image so that we can copy paste to our excel to analyze. Also post what you have tried yet. — Harun24hr, Feb 11 '21 at 05:48

Harun24hr · Answer 1 · 2021-02-11T06:06:36.893

You can try FILTERXML() function in this way.

=IFERROR(FILTERXML("<t><s>"&SUBSTITUTE(A1,".","</s><s>")&"</s></t>","//s[starts-with(., 's0')]"),"")

Here "<t><s>"&SUBSTITUTE(A1,".","</s><s>")&"</s></t>" will create a valid XML format string.
It seems your episode number alwas starts with s0. So, XPATH parameter //s[starts-with(., 's0')] will return that node starts with s0.
IFERROR() will return a zero length string if no episode is in your string.
If you want only episode numbers like e04, e01 and so on then use below formula.

=IFERROR(MID(FILTERXML("<t><s>"&SUBSTITUTE(A1,".","</s><s>")&"</s></t>","//s[starts-with(., 's0')]"),4,100),"")

1 Answers1