Formula to remove all text after second dash and third dash if third dash exist

[Sean15]

Hello:

An online poster posted gave this formula to remove all text after the second dash including the second dash.

LEFT(D19,FIND(" - ",D19,FIND(" - ",D19)+1)-1)

But can formula be adjusted to remove all text after second dash including the second dash and all text after third dash including the third dash if a third dash exist? I working with odd format data and that would be a big help.

 

[Candyman8019]

Give this a go:
=LEFT(D19,LEN(D19)-FIND("-",(SUBSTITUTE(D19,"-","-",2)))-1)

Change the 2 to a 3, etc.

 

[JEC]

So how does your data look like?

 

[Fluff]

But can formula be adjusted to remove all text after second dash including the second dash and all text after third dash including the third dash if a third dash exist?

Surely that's what it already does. It removes everything after the 2nd - which also means everything after the 3rd -

 

[Sean15]

@Candyman8019 - your formula is removing all text after first dash.

Data looks like:

XXXXXXX - Cash - 12/1/2021

XXXXXX - Dining Room - Food - 12/1/2021



Required values:

XXXXXXX - Cash

XXXXXX - Dining Room - Food

 

[Sean15]

@Fluff, sorry I didn't explain properly. See post #5

 

[JEC]

This will work but does your string always ends with a date?

=LEFT(SUBSTITUTE(A26,"-","#",LEN(A26)-LEN(SUBSTITUTE(A26,"-",""))),SEARCH("#",SUBSTITUTE(A26,"-","#",LEN(A26)-LEN(SUBSTITUTE(A26,"-",""))))-2)

When it does end with a date

=LEFT(A26,SEARCH("/",A26)-6)

 

[Fluff]

Ok, how about

=TEXTJOIN(" - ",,FILTERXML("<k><m>"&SUBSTITUTE(D19," - ","</m><m>")&"</m></k>","//m[position()<last()]"))

 

[Sean15]

Hello:

Thank you so much. I just saw a post at Exceljet with a workable logic, remove characters from the right

LEFT(D19,LEN(D19)-11)

Since I only have to remove 11 characters to the right, the formula suits my needs. But thank you so much for your help.

Sean

 

[Fluff]

Glad you sorted it & thanks for the feedback.