Formula Help to extract File Path & file name and file size from a single cell

[VBA learner ITG]

Hi Experts,

I hope you are well during this crazy period?

I have tried Google and stumped because of the special characters that are used as a seperator. I am sure you experts would have a more elegant way of accomplishing what I need.

I need to extract these values into their own columns.

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Campaign

HC-ICONIC-SLABS EMERGING FROM CHOC-PORTRAIT-EXTENDED_DARKER.jpg

32.324

From the below Text String in Excel in Cell A1:

J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"AUTUMN 19_CONTACT SHEET_LR.pdf","0.288"

Below is a list of other paths for examples

"L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Campaign","HC-ICONIC-SLABS EMERGING FROM CHOC-PORTRAIT-EXTENDED_DARKER.jpg","32.324"
"L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Campaign","HC-ICONIC-SLABS EMERGING FROM CHOC-PORTRAIT-EXTENDED_DARKER.psd","966.146"
"L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Campaign","HC-ICONIC-SLAB_FAN_SHOT-EXTENDED-D.psd","1732.891"
"L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Campaign","HC-ICONIC-SLAB_FAN_SHOT-EXTENDED.jpg","113.032"
"L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Catalogue 2019 Imagery","111988-BOX_PUMPKIN_HALLOWEEN_2019-D_S.psd","165.431"
"L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Catalogue 2019 Imagery","111990-OOZY_EYES_WHITE-PACK-2019-D_S.psd","68.073"
"L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Catalogue 2019 Imagery","112066-TIDDLY-VAMPIRES-MILK-2018-PACK.psd","6.64"
"L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Catalogue 2019 Imagery","260238-CHOOSE_YOUR_POISON_MINIDOMES-TRAY-OH.psd","5.049"
"L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Autumn\Images\Catalogue 2019 Imagery","260239-HALLOWEEN_HBOX-2019-TRAY-LID.psd","4.342"

 

[alansidman]

On the Data Tab, Text to Columns. Use the comma as the delimiter.

 

[VBA learner ITG]

On the Data Tab, Text to Columns. Use the comma as the delimiter.

@alansidman

Unfortunately, I have tried that method. In the below exmaple I have a Comma which is part of the file path.

I assume the only way of getting the exact value is by a formula which seperatesd the values by ","

So in this instance the example would be:

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat

IMG_1976.jpg

1.861

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1976.jpg","1.861"

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1978.jpg","2.837"

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1979.jpg","2.394"

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1980.jpg","2.597"

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1981.jpg","3.088"

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1982.jpg","3.884"

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1990.jpg","3.125"

L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1991.jpg","3.364"

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1976.jpg","1.861"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1976.jpg	1.861
3	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1978.jpg","2.837"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1978.jpg	2.837
4	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1979.jpg","2.394"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1979.jpg	2.394
5	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1980.jpg","2.597"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1980.jpg	2.597
6	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1981.jpg","3.088"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1981.jpg	3.088
7	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1982.jpg","3.884"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1982.jpg	3.884
8	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1990.jpg","3.125"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1990.jpg	3.125
9	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1991.jpg","3.364"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1991.jpg	3.364
Data

Cell Formulas
Range	Formula
B2:D9	B2	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",""","</m><m>"),"&","&amp;"),"""","")&"</m></k>","//m"))
Dynamic array formulas.

 

[VBA learner ITG]

How about

+Fluff 1.xlsm
	A	B	C	D
1
2	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1976.jpg","1.861"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1976.jpg	1.861
3	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1978.jpg","2.837"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1978.jpg	2.837
4	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1979.jpg","2.394"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1979.jpg	2.394
5	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1980.jpg","2.597"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1980.jpg	2.597
6	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1981.jpg","3.088"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1981.jpg	3.088
7	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1982.jpg","3.884"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1982.jpg	3.884
8	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1990.jpg","3.125"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1990.jpg	3.125
9	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images,"IMG_1991.jpg","3.364"	L:\hotel_chocolat_import_phase2\Artwork & Image Assets\Core\Rabot Hotel, from Hotel Chocolat\Images	IMG_1991.jpg	3.364
Data

Cell Formulas
Range	Formula
B2:D9	B2	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,",""","</m><m>"),"&","&amp;"),"""","")&"</m></k>","//m"))
Dynamic array formulas.

Hi @Fluff

Thank you for the solution this has worked as expected.

I dont fully understand the logic/formula that you have developed.

 

[Fluff]

Glad we could help & thanks for the feedback.