How to extract values after 2 types of character?

[Exhell_Simon]

Hi folks,

I'm hoping someone might have a formula for this use case. I have a sheet containing a column of data values. the values are like this:

Updated_New_Customer_DE
2016_w32_1_Sunday_ladies [example]

I would like to use a formula to extract the value either after the last underscore_ or the value contained within the [ ] square brackets. The length of what's contained or after the last _can vary. It sort of needs to work right to left I guess.

So the above would generate values of:

DE
example

Thanks


Simon

I have searched and can't find this previously asked.

Many Thanks


Simon

 

[Eric W]

Welcome to the board.

One option:

=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"[","_"),"]",""),"_",REPT(" ",LEN(A1))),LEN(A1)))

 

[Exhell_Simon]

Eric, a big thank you for such a speedy reply that did indeed work. My efforts so far involved some lashed up formula involving LEN and FIND but didn't work. Much appreciated.

 

[Special-K99]

Well i spent time on this so I may as well post it but I'd go with Erics solution as it's shorter

=IF(ISNUMBER(SEARCH("[",A1)),MID(A1,FIND("[",A1)+1,FIND("]",A1)-FIND("[",A1)-1),TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),LEN(A1))))

 

[Eric W]

Glad we could help.