Excel formula to return string between characters from right 1

[JGKWORK]

Hi,

I would like to return a string from between two characters using a formula (no VBA).

Problem is I have three characters in the string which are the same so I really want to return from the RIGHT e.g. this is how my strings look:

"Test_001_This is a Test_24-08-2011.doc"

and I wish to return:

"This is a Test"


Any help very much appreciated, thanks.

 

[kjv1611]

I'm not really sure what you mean "from the right". What you're wanting, is as expressed in your first statement, the character (or characters) from the middle.

So, you need to look at the MID() function for that.

If it is a set number of characters in length, and a set number of characters before the string that you want begins, then that's all you need.

However, if you also need those numbers to vary, then you'll need another formula or two. I forget if it's the FIND() function, or another "search" type function.

So for your example...

 

[kjv1611]

=MID("Test_001_This is a Test_24-08-2011.doc",10,14)

 

[SkipVought]

Hi,

If you ALWAYS want to return the string that is between the SECOND and THIRD UNDERSCORES then...
[tt]
=MID(A1,FIND("_",A1,FIND("_",A1,1)+1)+1,FIND("_",A1,FIND("_",A1,FIND("_",A1,1)+1)+1)-FIND("_",A1,FIND("_",A1,1)+1)-1)
[/tt]
assuming your text is in A1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

Yep, so it was the FIND function. I didn't bother testing to see.

 

[JGKWORK]

Many thanks Skip - that's spot on.

As for "from the RIGHT" - well that was just my non technical speak to try and leave out the first "_" on the left - never mind!

Many thanks all, really appreciate the help.

 

[SkipVought]

It is always better to clearly and concisely define your requirments. We all had to guess a little, with a ~90% confidence factor.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}