Returning text after the 2nd occurence of a particular character in string

Hi All,

I'm sure this has probably been done to death but for the life of me I cannot find a solution that fits my need.

I wish to return the characters after the 2nd "_" in a text string. However, this text string may or may not contain further "_" after the text that I want. For instance, for the values shown in the first column, I require the output shown in the 2nd collumn;

S1_358_801	801
S1_358_801_PR2	801
S1_370_733A	733A
S1_3701_722_BA1	722
S31_342_902	902

As can be seen the number of characters between "_" can change and I'm only after the characters after the 2nd "_" but before the 3rd "_" if it exists.

I have tried using a MID function but to no avail as of yet as occasionally the lenth changes, thus for the length I imagine I would have to use a combination of RIGHT, FIND, LEN and maybe TRIM functions but I dont know how to do this.

Any Assistance is appreciated.

Cheers,

Ben

hi,

Try This
=MID(L7,(FIND("_",L7,FIND("_",L7)+1)+1),(FIND("_",L7,FIND("_",L7,FIND("_",L7)+1)+1))-(FIND("_",L7,FIND("_",L7)+1)+1)),

This will work for second occurrence only.

Punnam

Thankyou FDibbins, your function was the first that worked for me but all of them are similar. However, could you explain why you use 99 as the 'value if true' for the IF function. Is this just a random number?

Cheers,

Ben

Hi spicey ,

Have tried the formula which i have given u, post reference is "2".

It worked fine for me .
Punnam

Sorry Punnam,

This doesnt work for me, no matter what I try I get a #VALUE error

Hi ,

just replace the cell address From L7 to u r required cell.

Punnam

hi,

it is working but partially need some changes .

Punnam

try this updated formula

=IFERROR(MID(A1,(FIND("_",A1,FIND("_",A1)+1)+1),(FIND("_",A1,FIND("_",A1,FIND("_",A1)+1)+1))-(FIND("_",A1,FIND("_",A1)+1)+1)),MID(A1,(FIND("_",A1,FIND("_",A1)+1)+1),LEN(A1)))

Punnam

As I said, FDibbins result has work best for me, I have also been able to adjust it a little for another application but thankyou for all your suggestions, It is all muchly appreciated.

Sorry about the delayed reply

I will now marked as solved.