Extracting text between certain words

[JJ EXCEL1234]

Hello, I have a worksheet that contains hyperlinks to hundreds of documents (Column B). The text string for hyperlinks under Column B contains volume and page numbers that I would like to extract and place in Column E.

(1) What formula would I need to extract the number after volume?

(2) What formula would I need to extract the number after page?

Possible Issue:
There hyperlink formulas should have similar formats under Column B, however there are 3 differences, there are formulas that only contain volume and page, ones that contain both volume and page and record number, and formulas which only contain record number.


Below is a picture showing a clip of my worksheet and the results I am looking for in the function.

https://imgur.com/a/Nic1hR6

 

[Scott Huish]

Any chance you could post a sample workbook rather than a picture of the workbook?

 

[JJ EXCEL1234]

Here is a sample workbook.

https://goo.gl/FszRBf

 

[jtakw]

Hi,

Here's a couple of ways.
1. Use E20 formula in E14 copied down if the "Volume" and "Page" is Always at the beginning.
2. Use E14 formula copied down if the "Volume" and "Page" might be somewhere Other than at the beginning and/or there might be Other "_" (underscores) within the text string.

Book1
	B	C	D	E
13	file link	county	instNumber	volume/page
14	volume-11_page-423_6fe5a853-b1e0-49fc-8c09-8ffd9e7aadb2.pdf	MIDLAND ABSTRACT PLANT		11/423
15	volume-688_page-53_record number-00011946_800bfc07-8abf-4520-ab2d-37daf9a4d15f.pdf	MIDLAND ABSTRACT PLANT	11946	688/53
16	record number-201821983_d77b2289-28c8-4db5-8c4b-a96edb7ce4df.pdf	MIDLAND ABSTRACT PLANT	201821983	NONE
17	record number-201823925_e7703b85-e14f-49c4-80cd-87a064075f2a.pdf	MIDLAND ABSTRACT PLANT	201823925	NONE
18	record number-201823926_eefa3464-e98f-4405-a6ac-9f0427e73d52.pdf	MIDLAND ABSTRACT PLANT	201823926	NONE
19	record number-201824074_d5306241-68f4-40c4-8289-a8dc3492f9cd.pdf	MIDLAND ABSTRACT PLANT	201824074	NONE
20				11/423
21				688/53
22				NONE
23				NONE
24				NONE
25				NONE
SAMPLE WORKBOOK

Cell Formulas
Range	Formula
E14	=IFERROR(TRIM(MID(SUBSTITUTE(B14,"_",REPT(" ",30)),SEARCH("Volume-",SUBSTITUTE(B14,"_",REPT(" ",30)))+7,30))&"/"&TRIM(MID(SUBSTITUTE(B14,"_",REPT(" ",30)),SEARCH("Page-",SUBSTITUTE(B14,"_",REPT(" ",30)))+5,30)),"NONE")
E20	=IFERROR(TRIM(MID(SUBSTITUTE(B14,"_",REPT(" ",30)),8,30))&"/"&TRIM(MID(SUBSTITUTE(B14,"_",REPT(" ",30),2),SEARCH("Page-",B14)+5,30)),"NONE")

 

[Peter_SSs]

Based on your samples data, two other ways.

A) Standard worksheet functions in E14

B) User-defined function in F14 (if you are interested further in this approach & need help with how to implement it, post back)

Function VolPage(s As String) As String
  VolPage = "NONE"
  With CreateObject("VBScript.RegExp")
    .Pattern = "(^.*volume-)(\d+)(_page-)(\d+)(.*$)"
    If .Test(s) Then VolPage = .Replace(s, "$2/$4")
  End With
End Function

Excel Workbook
	B	E	F
14	volume-11_page-423_6fe5a853-b1e0-49fc-8c09-8ffd9e7aadb2.pdf	11/423	11/423
15	volume-688_page-53_record number-00011946_800bfc07-8abf-4520-ab2d-37daf9a4d15f.pdf	688/53	688/53
16	record number-201821983_d77b2289-28c8-4db5-8c4b-a96edb7ce4df.pdf	NONE	NONE
17	record number-201823925_e7703b85-e14f-49c4-80cd-87a064075f2a.pdf	NONE	NONE
18	record number-201823926_eefa3464-e98f-4405-a6ac-9f0427e73d52.pdf	NONE	NONE
19	record number-201824074_d5306241-68f4-40c4-8289-a8dc3492f9cd.pdf	NONE	NONE
SAMPLE WORKBOOK

 

[Peter_SSs]

In case the volumn/page data is not always at the start of the text as jtakw suggested in relation to his E14 formula, this slight modification of my formula from the previous post would also allow for that (see row 17 below).
(The user-defined function posted previously works in this circumstance as it is too)

Excel Workbook
	B	E
14	volume-11_page-423_6fe5a853-b1e0-49fc-8c09-8ffd9e7aadb2.pdf	11/423
15	volume-688_page-53_record number-00011946_800bfc07-8abf-4520-ab2d-37daf9a4d15f.pdf	688/53
16	record number-201821983_d77b2289-28c8-4db5-8c4b-a96edb7ce4df.pdf	NONE
17	some other text volume-11_page-423_6fe5a853-b1e0-49fc-8c09-8ffd9e7aadb2.pdf	11/423
SAMPLE WORKBOOK

 

[JJ EXCEL1234]

I got it working! Thank you everybody for all the help. The formula I ended up using in the end was Peter_SS's.

 

[Peter_SSs]

I got it working! Thank you everybody for all the help.

You're welcome. Glad you got something suitable.