Excel - How get everything to the left of the second to last instance of a character

Question

How do I return everything to the left of the second to last occurrence of a character? In this case, the character is "-" For example:

1. "hello-baby-123-sun" I need to return "hello-baby"
2. "hello-baby-123-sun-shine-123", I need to return "hello-baby-123-sun"

Thanks!

I haven't been able to figure it out.

Answer 1

Try using TEXTBEFORE() function with the instance being -2

---

=TEXTBEFORE(A1:A2,"-",-2)

---

Alternatively, adding an error control is necessary:

---

=IFERROR(TEXTBEFORE(A1:A2,"-",-2),A1:A2)

---

For Older Versions, one can try one of the followings:

• Using LEFT() & FIND()

=IFERROR(
 LEFT(A1,
 FIND("|",
 SUBSTITUTE(A1,"-","|",
 LEN(A1)-LEN(SUBSTITUTE(A1,"-",))-1))-1),A1)

---

• Or Using FILTERXML()^╪ as mentioned in comments by @Harun24hr:

=IFERROR(
 INDEX(
 FILTERXML("<m><b>"&SUBSTITUTE(A1,"-","</b><b>",
 LEN(A1)-LEN(SUBSTITUTE(A1,"-",))-1)&"</b></m>","//b"),1),A1)

---

^{╪ Note: FILTERXML() is available from Excel 2013+ onwards as well as it is not applicable in Excel For The Web and Excel For MAC. TEXTBEFORE() works with MS365 Exclusively.}

---

^{One can learn more on FILTERXML() from the following link, nice article by JvdV Sir, Extract substring(s) from string using FILTERXML}