Right most word from a string

  • Thread starter Thread starter M.Siler
  • Start date Start date
M

M.Siler

I found the following formula that works but it seems overly complicated. My
goal is to just get the right most word from a string, for example if I have
"Now is the time from all good men" in A1 the formula I want in B1 should
return just the single word men. Is there a simpler formula that will do
this?


=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))
 
If you wrote it as a UDF, then you could just have something like:

=extract(A1)

where extract is the name you give to the UDF - this is certainly a lot
simpler!

Mind you, if you went to that trouble, then there are other ways of
extracting the word using VBA.

Pete
 
Not really. There a few variations of how the formula can be written but
they're all basically the same. This one saves 4 keystrokes:

=MID(A5,FIND("~",SUBSTITUTE(A5," ","~",LEN(A5)-LEN(SUBSTITUTE(A5,"
",""))))+1,255)

Biff
 
What is a UDF? I assume it is something where I write the fucntion extract
as Excel tells me Extract isn't a function. Can you explain a bit more?
 
Yes, it stands for User-Defined Function. You write this in VBA, but
you can then use it from a worksheet as if it were a standard function.
You can give it whatever name you like ("extract" was my example), and
pass parameters into it depending on how it has been defined. They tend
not be be as quick as using the built-in functions, so if you have
thousands of calls to the same function then the worksheet could appear
quite sluggish. By using VBA, though, you are able to do things that
you can't do very easily with functions, such as loops.

Chip Pearson has a few comments on UDFs here:

http://www.cpearson.com/excel/differen.htm

Hope this helps.

Pete
 
Back
Top