extracting text between 2 text strings

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
2,096
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi All

What is the best formula to get the number in red below, no matter what lenght the number may be.
Im confident the id= and the ">V will always be either side in green.


<tbody>
</tbody>
< a title="View Race" href="resultsRace.aspx?id=1942402">View Race
many thanks

Dave
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
Try this...
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
68,088
Office Version
  1. 365
Platform
  1. Windows
Looks like your last edit change the view of your data.
You originally had this:
Code:
id=1942402">View Race
If the value were in cell A1, this should get what you want:
Code:
=MID(A1,FIND("id=",A1)+3,FIND(">V",A1)-FIND("id=",A1)-4)
 
Upvote 0

AliGW

Banned
Joined
Mar 9, 2014
Messages
3,628
Try this:

=LEFT(SUBSTITUTE(A1,"id=",""),FIND(">",SUBSTITUTE(A1,"id=",""))-2)

EDIT: this will not work with the amended request -sorry.
 
Last edited:
Upvote 0

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
2,096
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
ADVERTISEMENT
dear all

sorry, it would not let me add the full string, but i think i have it now.

please see my edited post.

dave
 
Upvote 0

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
68,088
Office Version
  1. 365
Platform
  1. Windows
Both the formulas Ford and I posted seem to return what you are looking for.
 
Upvote 0

SQUIDD

Well-known Member
Joined
Jan 2, 2009
Messages
2,096
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Thanks all

sorry for the confusion.
Joe, i used yours in the end.

thanks again all.

Dave
 
Upvote 0

AlKey

Active Member
Joined
Oct 15, 2013
Messages
395
Here is another way

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,""">","|"),"id=","|"),"|",REPT(" ",99)),99,99))


Excel 2016 (Windows) 32 bit
AB
1< a title="View Race" href="resultsRace.aspx?id=1942402">View Race1942402
Sheet4
 
Upvote 0

Forum statistics

Threads
1,195,827
Messages
6,011,824
Members
441,648
Latest member
Bigjohnj

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
Top