Extract text before the last occurrence of a character

[rongrace]

I want to extract the data before the last occurrence of a character, for example, cell contains 'T10_Shorter_2016'. I want to extract 'T10_Shorter'

I tfound the following =LEFT(A1,FIND("_",SUBSTITUTE(A1,"#","_",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))-1) and tried to use it, but excel told me it wasn't a formula

I assume there's a problem with the syntax, but I can't see what

Any help would be appreciated

 

[Fluff]

Hi & welcome to MrExcel.
There is nothing wrong with that formula, however from your description I think it should be

=LEFT(A1,FIND("#",SUBSTITUTE(A1,"_","#",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))-1)

 

[rongrace]

Hi & welcome to MrExcel.
There is nothing wrong with that formula, however from your description I think it should be

=LEFT(A1,FIND("#",SUBSTITUTE(A1,"_","#",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))-1)

Hi Fluff, thanks for your help, that works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kcmuppet]

or in 365 you can use:

=TEXTBEFORE("T10_Shorter_2016","_",-1)

 

[Fluff]

You can use that now, but that function was not available back in Feb 2022.