Remove text in a string

[Melanie_B1]

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

 

[FDibbins]

is the basic format always the same?
text\/text\/what-you-want.xxx,text.....................

 

[Beyond_avarice]

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.

 

[Caleeco]

^^ Provided the format is the same....
This seems to be working. if your string is in cell A1, paste this into any cell.

=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

Caleeco

 

[Melanie_B1]

Yes there is always an ".aspx"
Thanks!

 

[Melanie_B1]

Seems I don't even know how to quote someone properly this morning either!

 

[Beyond_avarice]

=MID(A1,FIND("Pages\/",A1,1)+7,FIND(".aspx",A1,1)-FIND("Pages\/",A1,1)-7)

 

[Melanie_B1]

I'm so happy right now! THANK YOU

 

[Melanie_B1]

=MID(A1,FIND("Pages\/",A1,1)+7,FIND(".aspx",A1,1)-FIND("Pages\/",A1,1)-7)

Can I just ask what the '7' means?

 

[FDibbins]

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"