Extract A String Between Two Characters

[theaudioguy]

I'm stuck. I need a formula to extract data from between two characters.

For Example, In A1 I have this: COMP_PROG_v1_ABCD_01

I want to extract the value between the 3rd and 4th "_"'s. The number of "_"'s will be consistent but not the # of characters between them. My brain is tired of thinking. Thanks.

 

[Ron Coderre]

With
A1: blahblahtext moretext, Result1: 454654654, Result Number2: ABC, Result Number3: 445344

This regular formula returns the text between the first comma and first colon

B1: =TRIM(LEFT(SUBSTITUTE(MID($A1,FIND("|",SUBSTITUTE($A1,",","|",COLUMNS($B:B)))+1,LEN($A1)),":",REPT(" ",LEN($A1))),LEN($A1)))

Copy that cell across through D1 to return the next instances of those patterns.

The results will be:
B1: Result1
C1: Result Number2
D1: Result Number3

Is that something you can work with?

 

[Roscoe_11]

Thanks Ron! What a speedy response and it works perfectly!

Seriously, you're a superstar

 

[Ron Coderre]

I'm glad I could help...and thanks for the kind words.

 

[agimcgale]

Hi,

I have a similar query hoping somoene can help, I need to extract the names (JoeBloggs,JoeSoap, etc) from the following strings

00123100_JoeBloggs_01/07/2013_17.00_skills
00124411_JoeSoap_02/07/2013_13.00_skills

etc ..


Thanks,
Aiden

 

[VISHMAY]

I have a list of website
Like
/studentadmin/Login.aspx?RedirectURL=admin/default.asp?id=1/studentadmin/App_Themes/Default/StyleSheet.css
/Studentadmin/WebResource.axd
/Studentadmin/WebResource.axd
/studentadmin/images/ribbonBg.gif
I need Extension
That is After Last (.)Dot -/studentadmin/images/ribbonBg.gif
But Optinally before First ?(Question Mark) like Link 1 - /studentadmin/Login.aspx?RedirectURL=admin/default.asp?id=1
Please Post the Formula

 

[Michael M]

Try

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

 

[Rick Rothstein]

I have a list of website
Like
/studentadmin/Login.aspx?RedirectURL=admin/default.asp?id=1/studentadmin/App_Themes/Default/StyleSheet.css
/Studentadmin/WebResource.axd
/Studentadmin/WebResource.axd
/studentadmin/images/ribbonBg.gif
I need Extension
That is After Last (.)Dot -/studentadmin/images/ribbonBg.gif
But Optinally before First ?(Question Mark) like Link 1 - /studentadmin/Login.aspx?RedirectURL=admin/default.asp?id=1
Please Post the Formula

Give this formula a try...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("?",A1&"?")-1),".",REPT(" ",500)),500))

 

[VISHMAY]

Give this formula a try...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("?",A1&"?")-1),".",REPT(" ",500)),500))

Thank You Sir,
This was Exactly I was Looking For,It Really Help Me.
Thank You

VishmayShah

 

[VISHMAY]

Earlier You have solved my problem. I need Little more help.If Possible
Writting
You Gave me Formula
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("?",A1&"?")-1),".",REPT(" ",500)),500))
For:
list of website
Like
/studentadmin/Login.aspx?RedirectURL=admin/default.asp?id=1/studentadmin/App_Themes/Default/StyleSheet.css
/Studentadmin/WebResource.axd
/Studentadmin/WebResource.axd
/studentadmin/images/ribbonBg.gif

Addition in Requirement:
Some Few Mo Result Where There is no Dot(.) Like

/studentadmin/cgo
/gmf

The Result for This is

/studentadmin/cgo
/gmf

So It Should Be Blank

Please Update This Formula
Thank You

 

[Rick Rothstein]

Earlier You have solved my problem. I need Little more help.If Possible
Writting
You Gave me Formula
=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("?",A1&"?")-1),".",REPT(" ",500)),500))
For:
list of website
Like
/studentadmin/Login.aspx?RedirectURL=admin/default.asp?id=1/studentadmin/App_Themes/Default/StyleSheet.css
/Studentadmin/WebResource.axd
/Studentadmin/WebResource.axd
/studentadmin/images/ribbonBg.gif

Addition in Requirement:
Some Few Mo Result Where There is no Dot(.) Like

/studentadmin/cgo
/gmf

The Result for This is

/studentadmin/cgo
/gmf

So It Should Be Blank

Please Update This Formula
Thank You

I think this formula will do what you want and yet still cover all the possible combinations I can think of that you might have to account for...

=IF(ISNUMBER(FIND(".",LEFT(A1,FIND("?",A1&"?")-1))),TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("?",A1&"?")-1),".",REPT(" ",500)),500)),"")