Formula Help to Extracting Text between Special Characters

VBA learner ITG

VBA learner ITG

Active Member
Joined
Apr 18, 2017
Messages
267
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi Experts,

I hope you are well during this crazy period?

I have tried Google and I can only find solutions to which I would have to apply multiple formulas to accomplish what I need. I am sure you experts would have a more elegent way of accomplishing what I need.

I need to extract this value:

AUTUMN 19_CONTACT SHEET_LR.pdf

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"
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
With only a single example it is only possible to provide an answer, not a solution.
Excel Formula: 
=MID(LEFT(A1,FIND(".pdf",A1)+3),FIND("""",A1)+1,LEN(A1))
 
Upvote 0
jasonb75 said:
With only a single example it is only possible to provide an answer, not a solution.
Excel Formula: 
=MID(LEFT(A1,FIND(".pdf",A1)+3),FIND("""",A1)+1,LEN(A1))
Click to expand...
@jasonb75

Apoloigies, please see below snap shot of file names which could contain various file format ext.



J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"AUTUMN 19_CONTACT SHEET_LR.pdf","0.288"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"AUTUMN_2016-GROUP-S2.jpg","21.64"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"AUTUMN_2016-GROUP-S2_lr.jpg","0.982"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"AUTUMN_LIBRARY_BK-BOOZY_TOFFEE-EXTENDED-D-LAYERED.jpg","86.353"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"AUTUMN_LIBRARY_BK-BOOZY_TOFFEE-EXTENDED-D-LAYERED.psd","97.906"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"AUTUMN_LIBRARY_BK-MILK_CRUMBLE-EXTENDED-D-LAYERED.jpg","86.44"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AUTUMN-GROUP_SHOT-2017-V2-D-LAYERED.jpg","85.68"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AUTUMN-GROUP_SHOT-2018-UPDATED-D.psd","1109.779"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AUTUMN-GROUP_SHOT-2018-UPDATED-D_V2.psd","1108.823"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AUTUMN-GROUP_SHOT-2018-UPDATED-RGB.tif","199.455"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AUTUMN-GROUP_SHOT-2018-UPDATED.jpg","93.613"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AUTUMN-SLEEKSTER-HBOX-2017-D-LAYERED.jpg","35.506"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AUTUMN_NAKED_GROUP_2-D-LAYERED.jpg","27.383"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT1.jpg","23.943"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT1.tif","73.283"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT10.jpg","13.246"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT10.tif","48.169"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT10_Cream BG.jpg","1.485"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT10_Cream BG.tif","49.106"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT11.jpg","14.574"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT11.tif","52.333"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT12.jpg","14.854"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT12.tif","53.308"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT13.jpg","14.688"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT13.tif","52.101"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT13_Cream BG.jpg","1.871"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT13_Cream BG.tif","52.625"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT14.jpg","15.566"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT14.tif","46.929"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT15.jpg","15.444"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT15.tif","46.587"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT16.jpg","15.383"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT16.tif","45.607"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT17.jpg","15.412"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT17.tif","45.222"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT18.jpg","15.769"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT18.tif","48.26"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT1_Cream BG.jpg","3.324"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT2.jpg","22.638"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT2.tif","70.534"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT2_Cream BG.jpg","3.074"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT3.jpg","22.993"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT3.tif","71.706"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT3_Cream BG.jpg","3.075"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT4.jpg","26.698"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT4.tif","81.736"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT5.jpg","25.379"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT5.tif","78.958"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT6.jpg","25.063"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT6.tif","78.12"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT7.jpg","26.255"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT7.tif","81.05"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT8.jpg","24.703"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT8.tif","76.505"
J:\hotel_chocolat_import_phase1\Artwork & Image Assets\Autumn\Images\Campaign,"HC-AW-SHOT9.jpg","14.998"
 
Upvote 0
It appears that the format of the string is consistent, with just the filename and extension changing .This should work, based on the list of examples above.
Excel Formula: 
=MID(LEFT(A1,FIND(""",""",A1)-1),FIND("""",A1)+1,LEN(A1))

The formula looks for the first instances of " and "," then returns whatever is in between. If there are any other data formats with different positioning of the double quotes then it could fail.
 
Upvote 0
Solution
jasonb75 said:
It appears that the format of the string is consistent, with just the filename and extension changing .This should work, based on the list of examples above.
Excel Formula: 
=MID(LEFT(A1,FIND(""",""",A1)-1),FIND("""",A1)+1,LEN(A1))

The formula looks for the first instances of " and "," then returns whatever is in between. If there are any other data formats with different positioning of the double quotes then it could fail.
Click to expand...

@jasonb75

Just tested your formula and it worked as expected.

Thank you so much for your time today as it has helped allot.
 
Upvote 0
You must log in or register to reply here.

Similar threads

VBA learner ITG
  • Solved
Formula Help to extract File Path & file name and file size from a single cell
Replies
5
Views
715
Fluff
Fluff
A
  • Question
Vlookup formula to return specific text if cell contains match to list
Replies
3
Views
177
awoohaw
awoohaw
T
  • Solved
How to extract text from cell between "(" and ")" ?
Replies
3
Views
530
Fluff
Fluff
M
  • Question
Help Me Extract 2nd date from Cell
Replies
3
Views
231
DanteAmor
DanteAmor
F
  • Solved
How to find special/weird characters in Excel?
Replies
2
Views
568
FlashDota
F

Forum statistics

Threads
1,215,248
Messages
6,123,867
Members
449,130
Latest member
lolasmith

Share this page

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
Back
Top