Harvesting information from a Cell with a File Path and File Name in it

[itsrich]

Column AA starting in Cell 2 has data in it in the format: P:\VCarve\PreNesting\BATX165\7-27-21 BATX165 (1) v\BATX165 050-30-60-1-1.crv (these cells can be anywhere in the Workbook, they are just data to be worked with)

I have 1205 cells with the same sequence of info, but the text varies in length such as (I used Paste File Path to get this data into the column of cells):

P:\VCarve\PreNesting\SMMV1654 COMBINED\5-3-21 (1 CC, 1 T)\SMMV1654 (1 CC, 1 T) 100-72-166-1-1 SIDE PANELS ~ OTHER 2 ARE IN SUPERMARKET.crv​

P:\VCarve\PreNesting\OLV2010 ALL\9-28-21 (4 CC-25, 2 WT-25) v\OLV2010 (4 CC-25, 2 WT-25) 050-60-130-7-1.crv​

P:\VCarve\PreNesting\OLV186 ALL\10-5-21 (1CC,1SC,1T,1CC-25,1T-25) v\OLV186 050-60-78-1-1.crv​

P:\VCarve\PreNesting\OLV2010 ALL\9-28-21 (4 CC-25, 2 WT-25) v\OLV2010 (4 CC-25, 2 WT-25) 090-60-195-14-1 DRILL.crv​

I wish to harvest the data out of the file name so I can crunch numbers.

P:\VCarve\PreNesting\BATX165\7-27-21 BATX165 (1) v\BATX165 050-30-60-1-1.crv

I want to harvest into columns (hope the color is not to confusing)

1. Counts: info between ( & ) Example: 1
2. Guage: 050 could = 050, 063, 070, 080, 090, 098 100, 125, or 190
3. Length: 30 could = 1 to 260
4. Width: 60 could = 1 to 260
5. Count of Sheets: 1 could = 1 to 20
6. Drill/No Drill: the word DRILL (this may or may not be in the file name.)

I know it is a combination of LEN, SEARCH, MID/RIGHT/LEFT, FIND and I am sure others. I can figure it out, but I am asking for your help to save time.

Thank you for your help!

 

[earthworm]

Still not clear . Can you give the answer that you want into another cell from that above mentioned list

 

[itsrich]

Still not clear . Can you give the answer that you want into another cell from that above mentioned lis

2021.11.16 Mr Excel.xlsx
	A	B	C	D	E	F	G	H
1	Counts	Guage	Length	Width	Count of Sheets	Drill / No Drill		Data
2	1 CC, 1 T	100	72	166	1	No		P:\VCarve\PreNesting\SMMV1654 COMBINED\5-3-21 (1 CC, 1 T)\SMMV1654 (1 CC, 1 T) 100-72-166-1-1 SIDE PANELS ~ OTHER 2 ARE IN SUPERMARKET.crv
3	4 CC-25, 2 WT-25	050	60	130	7	No		P:\VCarve\PreNesting\OLV2010 ALL\9-28-21 (4 CC-25, 2 WT-25) v\OLV2010 (4 CC-25, 2 WT-25) 050-60-130-7-1.crv
4	1CC,1SC,1T,1CC-25,1T-25	050	60	78	1	No		P:\VCarve\PreNesting\OLV186 ALL\10-5-21 (1CC,1SC,1T,1CC-25,1T-25) v\OLV186 050-60-78-1-1.crv
5	4 CC-25, 2 WT-25	090	60	195	14	DRILL		P:\VCarve\PreNesting\OLV2010 ALL\9-28-21 (4 CC-25, 2 WT-25) v\OLV2010 (4 CC-25, 2 WT-25) 090-60-195-14-1 DRILL.crv
Sheet1

 

[itsrich]

Column A Formula =MID(I2,SEARCH("(",I2)+1,SEARCH(")",I2)-SEARCH("(",I2)-1)

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Counts	Guage	Length	Width	Count of Sheets	Drill / No Drill		Data
2	1 CC, 1 T	100	72	166	1	No		P:\VCarve\PreNesting\SMMV1654 COMBINED\5-3-21 (1 CC, 1 T)\SMMV1654 (1 CC, 1 T) 100-72-166-1-1 SIDE PANELS ~ OTHER 2 ARE IN SUPERMARKET.crv
3	4 CC-25, 2 WT-25	050	60	130	7	No		P:\VCarve\PreNesting\OLV2010 ALL\9-28-21 (4 CC-25, 2 WT-25) v\OLV2010 (4 CC-25, 2 WT-25) 050-60-130-7-1.crv
4	1CC,1SC,1T,1CC-25,1T-25	050	60	78	1	No		P:\VCarve\PreNesting\OLV186 ALL\10-5-21 (1CC,1SC,1T,1CC-25,1T-25) v\OLV186 050-60-78-1-1.crv
5	4 CC-25, 2 WT-25	090	60	195	14	Drill		P:\VCarve\PreNesting\OLV2010 ALL\9-28-21 (4 CC-25, 2 WT-25) v\OLV2010 (4 CC-25, 2 WT-25) 090-60-195-14-1 DRILL.crv
Lists

Cell Formulas
Range	Formula
A2:A5	A2	=MID(H2,SEARCH("(",H2)+1,SEARCH(")",H2)-SEARCH("(",H2)-1)
B2:E5	B2	=TRIM(MID(SUBSTITUTE(MID($H2,SEARCH(" ???-*-*-*-",$H2)+1,100),"-",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100))
F2:F5	F2	=IF(ISNUMBER(SEARCH("drill.",H2)),"Drill","No")

 

[itsrich]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Counts	Guage	Length	Width	Count of Sheets	Drill / No Drill		Data
2	1 CC, 1 T	100	72	166	1	No		P:\VCarve\PreNesting\SMMV1654 COMBINED\5-3-21 (1 CC, 1 T)\SMMV1654 (1 CC, 1 T) 100-72-166-1-1 SIDE PANELS ~ OTHER 2 ARE IN SUPERMARKET.crv
3	4 CC-25, 2 WT-25	050	60	130	7	No		P:\VCarve\PreNesting\OLV2010 ALL\9-28-21 (4 CC-25, 2 WT-25) v\OLV2010 (4 CC-25, 2 WT-25) 050-60-130-7-1.crv
4	1CC,1SC,1T,1CC-25,1T-25	050	60	78	1	No		P:\VCarve\PreNesting\OLV186 ALL\10-5-21 (1CC,1SC,1T,1CC-25,1T-25) v\OLV186 050-60-78-1-1.crv
5	4 CC-25, 2 WT-25	090	60	195	14	Drill		P:\VCarve\PreNesting\OLV2010 ALL\9-28-21 (4 CC-25, 2 WT-25) v\OLV2010 (4 CC-25, 2 WT-25) 090-60-195-14-1 DRILL.crv
Lists

Cell Formulas
Range	Formula
A2:A5	A2	=MID(H2,SEARCH("(",H2)+1,SEARCH(")",H2)-SEARCH("(",H2)-1)
B2:E5	B2	=TRIM(MID(SUBSTITUTE(MID($H2,SEARCH(" ???-*-*-*-",$H2)+1,100),"-",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100))
F2:F5	F2	=IF(ISNUMBER(SEARCH("drill.",H2)),"Drill","No")

TYVM ~ Seeing is very very good!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[itsrich]

You're welcome & thanks for the feedback.

Why did you use this COLUMNS($B2:B2)*100-99 to get 1? I noticed it counted up in the column to the right.

 

[Fluff]

By using that it returns the 1st part of the string in B2 & when dragged to the right gets the 2nd, then the 3rd etc.