Excel :: split string only by the first occurrence of a delimiter

Question

I have this file that I copy/pasted into Excel.

Sometimes the rows look like this:

 귀신 신 (spirit, ghost)

And I have to split by the character (

But sometimes the rows looks like this:

 눈 설.  Snow

And in that case I have to split by the character .

No problem I found a solution: =TEXTSPLIT(D2,{"(","."})

And it makes the job done. The problem arrives when I have rows that looks like this:

등급 급.  Grade. Rank.  Level.

In that case I need to slit but only by the first . and not all the 3 .

How to use TEXTSPLIT and split string only by the first ( or .

Answer 1

Try:

---

=TRIM(TEXTBEFORE(TEXTAFTER("."&SUBSTITUTE(A1:A5,"(",".",1),".",{1,2},,,"")&".",".",{1,-1}))

---