Remove text in a string

Melanie_B1

New Member
Joined
Aug 21, 2017
Messages
7
Hi there,

I am trying to eliminate text that has been returned in a report. This is what has been returned:

xyzcompany.sharepoint.com\/operations\/Pages\/F18-Week-6-Store-Managers-Operations-Update.aspx","UserId":"r.wood@totaltools.com.au","CustomUniqueId":false,"EventSource":"SharePoint","ItemType":"Page","ListId":"604f6a4a-685e-422d-befd-cfc55f935957","ListItemUniqueId":"2d65d1ff-a705-4198-a673-781d221c1b98","Site":"320aeeeb-716c-443a-8596-bd01b8f2a75b","UserAgent":"Mozilla\/5.0 (Windows NT 6.1; WOW64; Trident\/7.0; rv:11.0) like Gecko","WebId":"83380220-8290-4529-991d-daa4b2b20c18"}

Basically, all I want to see is:
F18-Week-6-Store-Managers-Operations-Update

I have gone through the threads to try to find the answer and I tried a couple of the examples but it didn't work for me. I'm barely an intermediate Excel user at best!

Many thanks,
M
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
is the basic format always the same?
text\/text\/what-you-want.xxx,text.....................
 
Upvote 0
If this is always consistent: xyzcompany.sharepoint.com\/operations\/Pages\/

Then we can start with a "MID()" formula.

Will there always be an ".aspx"? Knowing how your file name ends will help determine how long the name is for extraction.
 
Upvote 0
^^ Provided the format is the same....
This seems to be working. if your string is in cell A1, paste this into any cell.
Code:
=MID(A1,FIND("@",SUBSTITUTE(LEFT(A1,SEARCH(".aspx",A1)),"/","@",(LEN(LEFT(A1,SEARCH(".aspx",A1)))-LEN(SUBSTITUTE(LEFT(A1,SEARCH(".aspx",A1)),"/","")))/LEN("/")))+1,SEARCH(".aspx",A1)-FIND("@",SUBSTITUTE(LEFT(A1,SEARCH(".aspx",A1)),"/","@",(LEN(LEFT(A1,SEARCH(".aspx",A1)))-LEN(SUBSTITUTE(LEFT(A1,SEARCH(".aspx",A1)),"/","")))/LEN("/")))-1)

There may be far cleaner ways to do this, but I'm not that good with formulas :p

Caleeco
 
Last edited:
Upvote 0
Code:
=MID(A1,FIND("Pages\/",A1,1)+7,FIND(".aspx",A1,1)-FIND("Pages\/",A1,1)-7)
 
Last edited:
Upvote 0
7 is the number of characters in pages\/ . Find returns the position of the 1st character that matches, in the string.

Also, I would probably use SEARCH instead of FIND. It works the same but is not case sensitive, FIND is. So FIND page will find "page", but it wont find "Page"
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

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