Formula to extract substring between two characters

taywl

New Member
Joined
Feb 2, 2012
Messages
14
I have a list of path names in column A and would like to extract the file names which come after the last "/" and before the last "." to be placed in column B. I also want all "%20" to be replaced with a space.

For example, cell A1 contains:
Level%202/SB/C10%20illus/SIF%20BC%20SB2B%20C10%20(I)%20aw%20code.docx?d=wae252c17551145c7b4b3b69c4d44f532&csf=1&e=hb3jX4

Cell B1 should have:
SIF BC SB2B C10 (I) aw code

Is there a formula I can put in column B?

Thanks in advance!



 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Re: Need help with formula to extract substring between two characters

This is easiest to do in steps.
In B1, put the formula =TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",255)),255)) which gets the "after the last /"
in C1, =TRIM(LEFT(SUBSTITUTE(B1,".",REPT(" ",255)),255)) will get the "before the ." bit
and =SUBSTITUTE(C1,"%20", " ") will change the substring to spaces.

One could use a monster formula combining those, but it would be hard to edit.
 
Upvote 0
Re: Need help with formula to extract substring between two characters

Wow didn't expect a reply this fast! thanks for that!
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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