Extract A String Between Two Characters

theaudioguy

New Member
Joined
Jan 27, 2010
Messages
27
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.
 
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
Code:
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?
 
Upvote 0

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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
 
Upvote 0
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
 
Upvote 0
Try
Code:
=MID(A1,FIND("_",A1)+1,FIND("_",A1,1+FIND("_",A1))-FIND("_",A1)-1)
 
Upvote 0
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))
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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)),"")
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top