Extracting the last word from a string.

I have a list in excel like the one below.
The filename is always after the last "/".
What formula can I use to extract the filename?
The number of instances of "/" is not cosistent in every path string.


../ABCD/home/ABCD/EFGH/ciaran
../ABCD/home/ABCD/EFGH/bin/paul
../ABCD/home/james
../ABCD/home/ABCD/EFGH/bin/partition

The results I would like are:
ciaran
paul
james
partition
0
ISO
3/19/2010 12:58:31 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
964 Views

Similar Articles

[PageSpeed] 51

Hi,

Try this

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"/","*",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Ciarán" wrote:

> I have a list in excel like the one below.
> The filename is always after the last "/".
> What formula can I use to extract the filename?
> The number of instances of "/" is not cosistent in every path string.
> 
> 
> ../ABCD/home/ABCD/EFGH/ciaran
> ../ABCD/home/ABCD/EFGH/bin/paul
> ../ABCD/home/james
> ../ABCD/home/ABCD/EFGH/bin/partition
> 
> The results I would like are:
> ciaran
> paul
> james
> partition
> .
> 
0
Utf
3/19/2010 1:56:03 PM
=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",99)),99))


"Ciarán" wrote:

> I have a list in excel like the one below.
> The filename is always after the last "/".
> What formula can I use to extract the filename?
> The number of instances of "/" is not cosistent in every path string.
> 
> 
> ../ABCD/home/ABCD/EFGH/ciaran
> ../ABCD/home/ABCD/EFGH/bin/paul
> ../ABCD/home/james
> ../ABCD/home/ABCD/EFGH/bin/partition
> 
> The results I would like are:
> ciaran
> paul
> james
> partition
> .
> 
0
Utf
3/19/2010 2:21:01 PM
Reply:

Similar Artilces:

Word 7 autorecovery not working
Use Vista Ultimate & Office 2007. Autorecovery used to work but now am getting this error message when I try to execute Autorecovery: MS Office Word cannot open this file because it is an unsupported file type. Help appreciated. ...

open word file
Hi i would like to open word file using getopenfilename by using excel macro and copy the data to excel file. Sub test() Dim appWD As Word.Application Dim appWDS As Word.Document Set appWD = CreateObject("Word.Application.8") appWD.Visible = True filetoopen = Word.Application.GetOpenFilename("All Files (*.*),*.*") Workbooks.Open filetoopen end sub Here you go. This works. It's not exactly like your code, but you get the idea. The "text to be pasted" should be replaced with text from your Excel file. Let me know if you need help getting...

Word Hangup
I did something thwt gets a message"Do you want to allow the (Microsoft Office) program to make changes to your computer?" It didn'[t used to do thisd when I wanted to use Word. I have a new computer withWindows 7. How do I get back to my original settings? Ed Berg desaltbox24@yahoo.com ...

copy a pdf graph into a word document
I'm trying to copy and paste a graph from a pdf document into a Word document. My operating system is Windows Vista. I've tried to print from the pdf document but nothing happens. I have several graphs that I need and don't want to have to copy them all out by hand. Any help would be most gratefully accepted. My email address is: hokokeha@hotmail.com Thanks in advance Hi, BernieMurray, Is the real problem that you are unable to print the PDF? Are you using Word to try to work around that problem? -- Susan Ramlet -- please reply to the newsgroup so a...

How to get list of string for particular condt
hi, let consider, A table has following fields, name amount date I know to get sum of amount based for particular date condition be.. select sum(amount) as val from table_name where date='06-16-2010' Same as I want to get list of name for a particular date. How can I achieve this.. -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201006/1 On Jun 17, 11:25=A0am, "visweswaran28 via SQLMonster.com" <u60654@uwe> wrote: > hi, > > let consider, > > A table has following fields, ...

How do you stack 3 words in one cell
I am trying to figure out how to put the text "monthly rental pmnts" all in one cell so that they stack on top of each other. Any suggestions? Hi Format the cell text>Wrapped Then use Alt+Enter after each value where you want to start a new line -- ------- Regards Roger Govier "Tami Sawyer" <Tami Sawyer@discussions.microsoft.com> wrote in message news:91EB2775-03DE-4A5C-9E0E-1334B711FF52@microsoft.com... > I am trying to figure out how to put the text "monthly rental pmnts" all > in > one cell so that they stack on top ...

Extract from Second to fift number of a serie
Happen that I have a to download a long list from a database coming this way: 5103-80-3342 5111-85-3342 5210-88-3342... What I need to do is extract only 3 numbers from every one of them like this: 103 111 210 Could somebody help me to get this done. Thanks Fernando Gomez Fernando, In another column: =MID(A2, 2, 3) copied down with the fill handle. This can be converted to permanent values so you don't need the original stuff any more, or the formulas If your data isn't always of the same form, where the 2nd-4th characters are what you want, you'll have to give more ...

string concatenation
Please help me with this task: I would like to define each "cell value" a string of chars with a code name (for example, cell A1: coded name="101", val="The"; cell A2: code name=102, val="quick"; cell A3: coded name=103, val="brown"; cell A4 coded name=104, val="fox", etc.) Then some how on Cell B9 for example, I want to define this formular: "101" + "102" + "103" + "104" so that I can have the visible value in Cell B9 as: "The quick brown fox" And Cell B10, for example, anothe...

Extract Top 5 value from a pivot table
Hello, I have a a pivot table listing by country and months of the number of times a certain internet page has been reviewed. I would like to have a formula (x5) which would extract the 5 highest pages viewed for a given month and given country. I tried the Getpivotdata and sumproduct functions, but I am getting nowhere. Can you help? Thank you Could you provide a sample of your data? On 23 Kwi, 01:47, Andre C <Andre C...@discussions.microsoft.com> wrote: > Hello, > > I have a a pivot table listing by country and months of the number of times > a ...

referencing a workbook using string var = error9
I'm getting a subs out of range error for the following (section taken from sub)- For j = 1 To 4 c = "dataname" & Format(j) If j = 1 Or j = 2 Then u = v Else u = "TOTAL DATA" MsgBox c 'first iter gives dataname1 as i would expect MsgBox u 'first iter gives worksheet name in v as i would expect MsgBox Workbooks(c).Worksheets(u).Name 'generates error 9 Exit Sub With Workbooks(c).Worksheets(u) 'generates error 9 .Cells(1, 1).Value = "p" .Cells(1, 2).Value = "q .Cells(1, 3).Value = "r" ...

Moving paragraphs from MS Word to Excel
Hello, In excel is there an alternative to using format|wrap text? I was wondering if it's possible to insert text from a word document into excel and have lines automatically cut off after a specified limit of characters/"whole words" - as it would appear in MS Word. For instance, if the first paragraph in my word document had 580 characters, I'd like to paste it into excel. Then I figure you can use a combination of trim, mid, and other text functions (that I'm unaware of) to return a specific range of characters. Let's say cell A1 had 580 character paragraph, a...

How do I add the Helvetica font to Word 2007 Home?
The Helvetica font is not available or I can't find it. Is it available or in the existing software? Thanks. Helvetica is an Apple / MAC O/S font, like Arial is a Microsoft / Windows O/S font. The PC equivalent of Helvetica is Swiss 721. "ahahorse" <ahahorse@discussions.microsoft.com> wrote in message news:0C38E6A3-3622-4422-9E9F-F19F1668A8CB@microsoft.com... : The Helvetica font is not available or I can't find it. Is it available or : in the existing software? : : Thanks. Install the font with Windows. As far as I know, Helvetica isn'...

Vba excel add tables & shapes in word
Working with Office 2007 i build a word document from within excel. I start with a empty document, and sets the size and orientation from excel. I can add tables and shapes to the first page. When adding to second page tables are placed correct, but shapes are placed on first page. How do i get the shapes intended for page 2 to appear on page 2? Thanks -- H. C. The .Shapes.AddShape method (as well as the other .AddXXX methods) has an optional argument named Anchor. In order to place the shape anywhere other than the top left corner of page 1, you must supply a Range as the value...

Why is colour printouts not same colour as in word?
When I print my logo from Word the colour is correct. When I copy it to Publisher and print it from there the colour comes out wrong. What could be the problem? Try importing the image rather than copy/paste. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "DvdM" <DvdM@discussions.microsoft.com> wrote in message news:E59B7744-C2FA-4A06-9778-96690CFFFE86@microsoft.com... > When I print my logo from Word the colour is correct. When I copy it to > Publisher and print it from there the colour comes out wrong. What co...

Formatting of Excel OLEs in Word is messed up after updating links
I have a Word document with multiple Excel objects pasted and linked to their source files. Every time I try to update any of them, they revert to their original size they had when I first pasted them in, instead of the formatting I applied afterward. I've tried to fix this by editing the links, but the "Preserve formatting after update" box is greyed out on all of them. Is there another way to force these objects NOT to revert to original size every time I update? The way it is now sort of defeats the whole purpose of linking them in the first place. ...

Microsoft Word 2007
I have done something to my Microsoft Word 2007 program that has caused me alot of stress. I have used this program for 3 years without difficulties. In the last week I did something to make the Word program "redo the original settings." When I open up Microsoft Word 2007, the indentation was at 0, now it is at 1.5 The spacing was at 0 and now it is at 8pt. The page numberings that aligned to the left, right or center are all off..maybe 5 or 6 spaces where they should be. I don't know what I did however, I know I must have done something. My older documents are f...

Spell Check should notify me if I keep spelling a word wrong
Spell Check is a fantastic tool, however, is not helping us to improve our grammar and spelling. As more and more children are using computers as a tool for education, their misspelled words are constantly being corrected (sometimes automatically) by Word which is not encouraging them to learn how to spell and use grammar correctly. It would be great if Word could notify us if we keep spelling the same word incorrectly or keep making the same grammar errors. I would like to see a pop up that indicated how many times I have spelled a word incorrectly. As an advanced tool it...

Ctrl+Backspace spits out squares rather than deletes words (Outlook 2007)
I'm re-posting this because I incorrectly mentioned that this problem was resolved. It is not resolved. And I now have Office 2007 installed on my workstation at the office and the exact same symptom is on that machine. It never saw the beta version of Office 2007. -- I have gotten into the habit of hitting Ctrl+Backspace to delete entire words of recently typed text. This is a great time saver. Unfortunately, in Outlook 2007, I am seeing the e-mail editor spit out squares (i.e. valueless Unicode character blocks). It's erratic; I can hold Ctrl down and keep hitting Backspace and...

Looping through every word in doc
Hi. I have a routine that loops through every word in the active document, sentence by sentence. Here is the code outline: For Each rngRange In ActiveDocument.StoryRanges For Each rngSentence In rngRange.Sentences For Each rngWord In rngSentence.Words MyWordProcess(rngWord) Next rngWord Next rngSentence Next rngRange For Each shpShape In ActiveDocument.Shapes If shpShape.TextFrame.HasText Then rngRange = shpShape.TextFrame.TextRange For Each rngSentence In rngRange.Sentences For Each rngWord In rngSentence.Words MyWordProcess(rngWo...

Two users on a network editing documents simultaneously in word 20
My office just upgraded from Word 2003 to Word 2007. We are on a network and share access to Word files. In Word 2003, if one user tried to open a file in which another user was working, a message would appear stating that the file was "locked for editing by (user)." It seems that with Word 2007, any user can open a file (and edit it) while another user is doing so as well. We do not want this to be the case, and just want the settings to be as they were with Word 2003 (i.e. if a document is in use, another user cannot access it simultaneously). Ideally, we'd lik...

unable to access my recent documents icon in word and publisher
unable to access my recent documents icon in word and publisher- HOW CAN i TURN ON RECENT DOCUMENT FEATURE? Perhaps you are running one of those Windows utilities that clears all kinds of history lists? If so, turn it off. -- Stefan Blom Microsoft Word MVP "Oliviachamp" wrote: > unable to access my recent documents icon in word and publisher- HOW CAN i > TURN ON RECENT DOCUMENT FEATURE? ...

Word bug: http hyperlinks to pdf docs open in IE, not default browser
In Word 2003 SP3, hyperlinks to PDF documents open in Internet Explorer, even though IE is not the system default browser. An adjacent hyperlink to a ".html" file in the same Word document opens in Firefox, as appropriate. This is a duplicate of an unresolved bug from Word 2002, reported here (as one of many archives for this group) http://www.eggheadcafe.com/software/aspnet/31307531/word-bug--http-hyperlink.aspx Have you been into Control Panel | Program and Features | Turn Windows features on or off and disabled IE? -- Terry Farrell - MSWord MVP "WBT"...

Word 2007 Envelope Issue
When trying to print an envelope from a letter that I compsed, I click the Mailings tab then the Envelope icon. The name and address from the letter no longer displays in the Envelope field since I had Office 2007 installed. Can you tell me why and is there a fix for this? ...

report or query to show when item was last received in purchase or
hello, i would like help to find out when was the last date i received an item in a purchase order. not when was transferred but when it was received. please help.... This query will give you the answer: select * from purchaseorderentry e join purchaseorder p on e.storeid=p.storeid and e.purchaseorderid=p.id where potype=1 and quantityreceivedtodate>0 and itemdescription='XYZdescription' "RP" <RP@discussions.microsoft.com> wrote in message news:380A0AB4-0141-4BBC-950B-ECE34CFFD8E5@microsoft.com... > hello, > > i would like help to ...

Can you help me make this string shorter?
I get an error message saying that my string is too complex. Can anyone see a way to streamline it?? =IIf(IsNull([costcode]) Or IsNull([costtype]),0,IIf([costcode]="013210" And [costtype]="05320",[txtEstimatedCost]/[txtCraftLabEst],IIf([costcode]="020110" And [costtype]="05320",[txtEstimatedCost]/[txtFirewatchLabEst],IIf([costcode]="064201" And [costtype]="05320",[txtEstimatedCost]/[txtCraftSuprLabEst],IIf([costcode]="061301" And [costtype]="05320",[txtEstimatedCost]/[txtQAQCSuprvLabEst],IIf([co...