Extracting text within varying length phrases

[Stu2407]

I have a massive file (53,000+ rows) where I need to extract a “Record ID#” value that is contained within text. The attached screenshot shows the various ways the Record ID# value shows up in the Comments

End goal is column B in attached screenshot.

I can NOT use macros as I need to show/teach this using formulas or power query to others who need duplicate this in various ways to deal with similar data cleanup situations.

• The only common delimiter is “2023/Archive/” everything else that follows ranges in length.
• The 10 character Record ID# always starts with a capital “D” followed by 9 digits OR a capital “D” followed by another capital letter then 8 digits.
• Most of the cells contain only 1 Record ID# in the Comments field.
• Some cells (A8 & A15) where Record ID# values are a range, the delimiter between the Record ID# range is Space/Dash/Space.

I tried the following but it failed--

• Find & Replace “2023/Archive/” to reduce clutter
• Then Text to Column’ed which gave me upto 5 columns of data for each row depending on length of comment.
• The Record ID# value could be in Column 1,2,3,4 or 5. I’d sort each column & delete other “junk” values… very time consuming.
• Then I didn’t know how to “bring over” that Record ID# value in either Column 1,2,3,4 or 5 into a new Record ID# column.
• The other issue with Text to Column’ing are Comments value (see A8-A15) where the Record ID# is within a phrase and/or the Record ID# is listed as a range instead of a single value.

I also tried to using SEARCH & RIGHT with “D” as trigger… nested formula was kicking my butt. Thought was I’d search for “D” in each cell and then give me 23 characters to the right. 23 refers to the characters when Record ID# is a range (see A8 & A15) & not the commonly occurring 9 digit value. Thought was I get a column with Record ID# followed by junk characters OR the 2nd half of a Record ID# range value. Then figure out how to delete the junk characters

I couldn’t think of how to Power Query to solve this either. I essentially did the same as above- split values by “/”. But still had the issues mentioned above to proceed with further cleanup to isolate the Record ID#.

Thanks in advance for any help you can provide.

 

[duggie33]

Please provide some sample data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data, allowing us to copy/paste it to our Excel spreadsheets to work with the same data you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that this board also has a "Test Here” forum. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thanks,

Doug

 

[Peter_SSs]

Welcome to the MrExcel board!

Certainly sample data with XL2BB would help in the future, however see if this helps. Not sure how robust this will be - depends on the (in)consistency of your data.

Stu2407.xlsm
	A	B
1	Data	Result
2	2023/Archive/Archive/Area-01/Group-01/D123456789	D123456789
3	2023/Archive/Area-01/Grou -02/D023456790	D023456790
4	2023/Archive/D123456791	D123456791
5	2023/Archive/Area-04/D123456792	D123456792
6	2023/Archive/DR23456793	DR23456793
7	2023/Archive/Area-014/D923456794	D923456794
8	2023/Archive/Area-031/D123456795 - D187156795	D123456795 - D187156795
9	2023/Archive/New ref# L26199A-01 (old ref# DM23456796)	DM23456796
10	2023/Archive/Area-01/Group-01/D123456797	D123456797
11	2023/Archive/Topic A/ This is a sample comment for D333456796	D333456796
12	2023/Archive/Topic B/Topic C/Variation for DQ33456325 sample comment	DQ33456325
13	2023/Archive/Topic D/ Sample data comment for old record id# D221100987 referenced	D221100987
14	2023/Archive/Samp1e comment for Mike DeWitterman with no record id# referenced
15	2023/Archive/Another sample comment for D923555680 - D923555713 contained within a large string of text	D923555680 - D923555713
Sheet1

Cell Formulas
Range	Formula
B2:B15	B2	=LET(id,"D?11111111",c,MID(A2,SEQUENCE(LEN(A2)),1),s,CONCAT(IF(ISNUMBER(c+0),1,c)),p,SEARCH(id,s),IFERROR(MID(A2,p,IF(ISNUMBER(SEARCH(id,s,p+9)),23,10)),""))

 

[duggie33]

Hi Peter,

In your formula, I like the idea of converting all the numbers "1" so pattern matching is easier.

Doug

 

[Stu2407]

Welcome to the MrExcel board!

Certainly sample data with XL2BB would help in the future, however see if this helps. Not sure how robust this will be - depends on the (in)consistency of your data.

Stu2407.xlsm
	A	B
1	Data	Result
2	2023/Archive/Archive/Area-01/Group-01/D123456789	D123456789
3	2023/Archive/Area-01/Grou -02/D023456790	D023456790
4	2023/Archive/D123456791	D123456791
5	2023/Archive/Area-04/D123456792	D123456792
6	2023/Archive/DR23456793	DR23456793
7	2023/Archive/Area-014/D923456794	D923456794
8	2023/Archive/Area-031/D123456795 - D187156795	D123456795 - D187156795
9	2023/Archive/New ref# L26199A-01 (old ref# DM23456796)	DM23456796
10	2023/Archive/Area-01/Group-01/D123456797	D123456797
11	2023/Archive/Topic A/ This is a sample comment for D333456796	D333456796
12	2023/Archive/Topic B/Topic C/Variation for DQ33456325 sample comment	DQ33456325
13	2023/Archive/Topic D/ Sample data comment for old record id# D221100987 referenced	D221100987
14	2023/Archive/Samp1e comment for Mike DeWitterman with no record id# referenced
15	2023/Archive/Another sample comment for D923555680 - D923555713 contained within a large string of text	D923555680 - D923555713
Sheet1

Cell Formulas
Range	Formula
B2:B15	B2	=LET(id,"D?11111111",c,MID(A2,SEQUENCE(LEN(A2)),1),s,CONCAT(IF(ISNUMBER(c+0),1,c)),p,SEARCH(id,s),IFERROR(MID(A2,p,IF(ISNUMBER(SEARCH(id,s,p+9)),23,10)),""))

Thanks Doug. I will install the add-on & get back to you. I'm sure I'll have some follow up how to questions.

 

[Stu2407]

Thanks Doug. I will install the add-on & get back to you. I'm sure I'll have some follow up how to questions.

Thanks Peter for the solution... typing while on the train

 

[Stu2407]

Hi Peter,

Could you give me the formula for just extracting the single value of the Record ID# & exclude the range values? I’ve never used some the functions in your formula so I’m trying to break it down to follow along… kind of getting lost in the nested functions. Formula for extracting the single value of the Record ID may be easier to break down & learn.

 

[duggie33]

I had already done a partial breakdown of Peter's LET formula...I added a few columns to try to make it clearer.

Book_2023-10-03.xlsm
	A	B	C	D	E	F	G	H	I
1	Data	Result	id	c	s_partA	s	p	final_partA	final
2	2023/Archive/Archive/Area-01/Group-01/D123456789/abc	D123456789	D?11111111	2	TRUE	1111/Archive/Archive/Area-11/Group-11/D111111111/abc	39	9	D123456789
3				0	TRUE
4				2	TRUE
5				3	TRUE
6				/	FALSE
7				A	FALSE
8				r	FALSE
9				c	FALSE
10				h	FALSE
11				i	FALSE
12				v	FALSE
13				e	FALSE
14				/	FALSE
15				A	FALSE
16				r	FALSE
17				c	FALSE
18				h	FALSE
19				i	FALSE
20				v	FALSE
21				e	FALSE
22				/	FALSE
23				A	FALSE
24				r	FALSE
25				e	FALSE
26				a	FALSE
27				-	FALSE
28				0	TRUE
29				1	TRUE
30				/	FALSE
31				G	FALSE
32				r	FALSE
33				o	FALSE
34				u	FALSE
35				p	FALSE
36				-	FALSE
37				0	TRUE
38				1	TRUE
39				/	FALSE
40				D	FALSE
41				1	TRUE
42				2	TRUE
43				3	TRUE
44				4	TRUE
45				5	TRUE
46				6	TRUE
47				7	TRUE
48				8	TRUE
49				9	TRUE
50				/	FALSE
51				a	FALSE
52				b	FALSE
53				c	FALSE
Sheet5

Cell Formulas
Range	Formula
B2	B2	=LET(id,"D?11111111",c,MID(A2,SEQUENCE(LEN(A2)),1),s,CONCAT(IF(ISNUMBER(c+0),1,c)),p,SEARCH(id,s),IFERROR(MID(A2,p,IF(ISNUMBER(SEARCH(id,s,p+9)),23,10)),""))
D2:D53	D2	=MID(A2,SEQUENCE(LEN(A2)),1)
E2:E53	E2	=ISNUMBER(D2#+0)
F2	F2	=CONCAT(IF(ISNUMBER(D2#*1),1,D2#))
G2	G2	=SEARCH("D?11111111",F2,1)
H2	H2	=IF(ISNUMBER(SEARCH("D?11111111",F2,G2+9)),23,9)
I2	I2	=IFERROR(MID(A2,G2,IF(ISNUMBER(SEARCH(C2,F2,G2+9)),23,10)),"")
Dynamic array formulas.

Hope that helps,

Doug

 

[Peter_SSs]

Could you give me the formula for just extracting the single value of the Record ID# & exclude the range values?

Not certain what you mean. This extracts the first id (if there is one).
If you want to actually exclude the rows with an id range, then the formula may actually be more complicated than the original post #3 formula & therefore defeat the idea of simplifying to break down & understand.

Stu2407.xlsm
	A	B
1	Data	Result
2	2023/Archive/Archive/Area-01/Group-01/D123456789	D123456789
3	2023/Archive/Area-01/Grou -02/D023456790	D023456790
4	2023/Archive/D123456791	D123456791
5	2023/Archive/Area-04/D123456792	D123456792
6	2023/Archive/DR23456793	DR23456793
7	2023/Archive/Area-014/D923456794	D923456794
8	2023/Archive/Area-031/D123456795 - D187156795	D123456795
9	2023/Archive/New ref# L26199A-01 (old ref# DM23456796)	DM23456796
10	2023/Archive/Area-01/Group-01/D123456797	D123456797
11	2023/Archive/Topic A/ This is a sample comment for D333456796	D333456796
12	2023/Archive/Topic B/Topic C/Variation for DQ33456325 sample comment	DQ33456325
13	2023/Archive/Topic D/ Sample data comment for old record id# D221100987 referenced	D221100987
14	2023/Archive/Samp1e comment for Mike DeWitterman with no record id# referenced
15	2023/Archive/Another sample comment for D923555680 - D923555713 contained within a large string of text	D923555680
Sheet3

Cell Formulas
Range	Formula
B2:B15	B2	=LET(c,MID(A2,SEQUENCE(LEN(A2)),1),IFERROR(MID(A2,SEARCH("D?11111111",CONCAT(IF(ISNUMBER(c+0),1,c))),10),""))