How to extract right-most word?

[nomail1983]

How can I extract the right-most "word" from a cell without knowing
its length, how many words are in the cell, or what the word is and
how many times it might occur in the cell?

I define a "word" to be a string of characters delimited by blanks.
To keep things simple, assume that there is always at least one blank
to the left of the last word.

For example, if A1 contains "now is the time", I want a formula in B1
whose result is "time".

I would be content with the following paradigm (which does not work):

=right(A1, len(A1) - find(A1, " ", -len(A1))

In other words, I want a use of FIND() or other function that searches
from the right instead of the left. (Specifying a negative starting
position might be one way to design it, in theory.)

The following paradigm is __not__ acceptable for my purposes, even
though it works in this particular example:

=right(A1, len(A1) - find(A1, "time") + 1)

"It cannot be done otherwise" is an acceptable, albeit undesirable
answer.

 

[Pete_UK]

Do a Google search on this group for "extract last name" - you'll get
plenty of solutions to this problem.

Hope this helps.

Pete

 

[nomail1983]

How can I extract the right-most "word" from a cell without knowing
its length, how many words are in the cell, or what the word is and
how many times it might occur in the cell?

I should have added: "and without resorting to VBA".

Do a Google search on this group for "extract last name" - you'll get
plenty of solutions to this problem.

Thanks. The best I have found so far that meets my criteria is:

=right(A1, len(A1) - find("*", substitute(A1, " ", "*", len(A1) -
len(substitute(A1, " ", "")))))

(Assuming that "*" is part of the text in A1.) Whew! That's a lot
of work.

 

[Rick Rothstein \(MVP - VB\)]

How can I extract the right-most "word" from a cell without knowing

its length, how many words are in the cell, or what the word is and
how many times it might occur in the cell?

I define a "word" to be a string of characters delimited by blanks.
To keep things simple, assume that there is always at least one blank
to the left of the last word.

For example, if A1 contains "now is the time", I want a formula in B1
whose result is "time".

I would be content with the following paradigm (which does not work):

=right(A1, len(A1) - find(A1, " ", -len(A1))

In other words, I want a use of FIND() or other function that searches
from the right instead of the left. (Specifying a negative starting
position might be one way to design it, in theory.)

The following paradigm is __not__ acceptable for my purposes, even
though it works in this particular example:

=right(A1, len(A1) - find(A1, "time") + 1)

"It cannot be done otherwise" is an acceptable, albeit undesirable
answer.

Is this formula acceptable to you?

=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))),9999)

Note the use of the vertical bar (|) which is supposed to be a character the
will never be in your text; the 9999 is just to make sure the MID function
looks up to the last character in the string.

Rick

 

[Ron Coderre]

Try one of these:

Array formula (committed with Ctrl+Shift+Enter, instead of just Enter):
=TRIM(VLOOKUP(" *",RIGHT(A1,ROW($A$1:INDEX($A:$A,LEN(A1),1))),1,0))

or this regular formula (committed with just Enter)
=TRIM(VLOOKUP(" *",INDEX(RIGHT(A1,ROW($A$1:INDEX($A:$A,LEN(A1),1))),0),1,0))

or this regular formula
=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Pete_UK]

Rather than an asterisk (which can be confusing as it is a wildcard
character) you can use something which is not likely to occur in your
word, eg "^^" or "~~" or "$$" (I've seen all three, though "~" also
has a special meaning). Basically, the formula finds how many spaces
there are in the text, then determines where the last space is, so
that can be used in conjuction with RIGHT.

Pete

 

[Rick Rothstein \(MVP - VB\)]

How can I extract the right-most "word" from a cell without knowing

I should have added: "and without resorting to VBA".

Why? Although slower than built-in functions, your strings are probably
small enough to minimize this effect, plus the VBA function is reasonable
small. Add a module to the worksheet and put this in the code window...

Function LastWord(R As Range) As String
Dim TextLine As String
LastWord = Split(R.Text)(UBound(Split(R.Text)))
End Function

Then, in your spreadsheet, you could just to this... =LASTWORD(A1)

Thanks. The best I have found so far that meets my criteria is:

=right(A1, len(A1) - find("*", substitute(A1, " ", "*", len(A1) -
len(substitute(A1, " ", "")))))

(Assuming that "*" is part of the text in A1.) Whew! That's a lot
of work.

I presume you meant "Assuming that "*" is NOT part of the text in A1". You
now have a couple of other choices posted in this thread for your
consideration.


Rick

 

[Ron Coderre]

If there's a concern that any of the usual matching characters may be in the
source text....use CHAR(7)….the ASCII Bell….instead.

Example:
=MID(A1,FIND(CHAR(7),SUBSTITUTE(A1," ",CHAR(7),LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))+1,255)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Rosenfeld]

How can I extract the right-most "word" from a cell without knowing
its length, how many words are in the cell, or what the word is and
how many times it might occur in the cell?

I define a "word" to be a string of characters delimited by blanks.
To keep things simple, assume that there is always at least one blank
to the left of the last word.

For example, if A1 contains "now is the time", I want a formula in B1
whose result is "time".

I would be content with the following paradigm (which does not work):

=right(A1, len(A1) - find(A1, " ", -len(A1))

In other words, I want a use of FIND() or other function that searches
from the right instead of the left. (Specifying a negative starting
position might be one way to design it, in theory.)

The following paradigm is __not__ acceptable for my purposes, even
though it works in this particular example:

=right(A1, len(A1) - find(A1, "time") + 1)

"It cannot be done otherwise" is an acceptable, albeit undesirable
answer.

This formula will give you the last word in the string, so long as there are at
least two words.

=IF(ISERR(FIND(" ",A1)),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255))




--ron

 

[Rick Rothstein \(MVP - VB\)]

Is this formula acceptable to you?

=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))),9999)

Note the use of the vertical bar (|) which is supposed to be a character
the will never be in your text; the 9999 is just to make sure the MID
function looks up to the last character in the string.

The above formula was broken (in my newsreader) at an unfortunate location
(the blank space in the SUBSTITUTE command. In addition, I just noticed it
has a leading blank space in the answer it produces (fixable with a TRIM
function call). But, after reading Ron's comment about his formula needing
two words to work, I noticed mine has the same problem. So, here is a
formula that addresses the above problems (I forced the break point, added
the Trim and made it so it will work if only one word is provided)...

=TRIM(MID(" "&A1,FIND("|",SUBSTITUTE(" "&A1," ","|",
LEN(" "&A1)-LEN(SUBSTITUTE(" "&A1," ","")))),9999))

Rick

 

[nomail1983]

How can I extract the right-most "word" from a cell without knowing
its length, how many words are in the cell, or what the word is and
how many times it might occur in the cell?

Thanks to all for the many variations on the same theme, namely:
counting the number of blanks, replacing that last instance of a
blank, then finding the replacement character. I guess Excel truly
does not have a "scan from the right" function. (Sigh.)

Although I would have no problem finding a unique displayable
replacement character, I do like the idea of using a non-displayable
character (e.g. char(1)) as a rule.

"Why not use VBA?" Because I might send the xls file to a novice user
who would not know what to do with the prompt about macro security.
Also, it's the principle of the matter: I can program almost any
solution in VBA (well, when I learn VBA better ;->), but for my
edification, I like to know when that is and is not necessary, for the
purpose of my understanding the limitations (or not) of Excel.

Finally, Rick observes in response to my posting:

I presume you meant "Assuming that "*" is NOT part of the text in A1".

Klunk! If I had a dollar for every time I make that mistake, I'd be
as rich as Bill Gates. Well, maybe. Oh, I mean "maybe NOT" ;-).

Seriously, that is one of my most common mistakes, despite my
"careful" proofreading specifically for it. I cannot tell you how
many embarrassing moments it has created in the past. I should have a
disclaimer in my signature: "Please insert the word 'not' wherever
you think I omitted it inadvertently" ;-). Thanks to all for
understanding my intended meaning.

 

[Harlan Grove]

Why? Although slower than built-in functions, your strings are probably
small enough to minimize this effect, plus the VBA function is reasonable
small. Add a module to the worksheet and put this in the code window...

....

?

First, the slowness of udfs has little to do with the size of the arguments,
only a little to do with the complexity of the VBA code, and mostly to do
with the slowness of the Excel to VBA interface.

And you have to enable macros in order to use udfs, so you either need to
sacrifice macro security or sign your own modules in order to use udfs.

 

[Harlan Grove]

How can I extract the right-most "word" from a cell without knowing
its length, how many words are in the cell, or what the word is and
how many times it might occur in the cell?

I define a "word" to be a string of characters delimited by blanks.
To keep things simple, assume that there is always at least one blank
to the left of the last word.

....

Something a bit different. Using the defined name seq referring to the
formula

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,256,1))

you could use the formula

=MID(TRIM(x),LOOKUP(2,1/(MID(TRIM(x),seq,1)=" "),seq)+1,256)

 

[T. Valko]

Something a bit different. Using the defined name seq referring to the
formula

=ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,256,1))

Why index such a large range when the effective range is only 1:256?

I need more insight on the general characteristics of INDEX. How does Excel
handle this in memory for calculation? You can't see how this is done by
evaluating a formula. For example: INDEX(B:B......

Does it index the entire range or just the used range? My gut tells me it's
the used range but I'd like to know for certain.

 

[Lori]

Another way: copy column then Edit > Replace "* " with blank

 

[Harlan Grove]

Why index such a large range when the effective range is only 1:256?

....

Because $1:$65536 is the only range reference that's completely unaffected
by inserting or deleting rows/columns.

Does it index the entire range or just the used range? My gut tells me it's
the used range but I'd like to know for certain.

....

The entire range.

This does have the drawback of triggering a lot of formula recalcs, so it's
actually better to create a dummy worksheet that would remain blank and be
hidden, then define seq as

=ROW(INDEX(dummy!$1:$65536,1,1):INDEX(dummy!$1:$65536,256,1))

Excel doesn't evaluate INDEX(..):INDEX(..) as a huge array of values, it
just evaluates it as a derived range reference. When passed to ROW, that
function doesn't use its values, just its shape/size as a range.

Given this, it'd be possible to define seq as

=ROW(dummy!$1:$256)

instead, but the first form could be generalized by using another defined
name like N, which could be a named cell which the user could change, and
modifying the definition of seq to

=ROW(INDEX(dummy!$1:$65536,1,1):INDEX(dummy!$1:$65536,N,1))

 

[Harlan Grove]

Another way: copy column then Edit > Replace "* " with blank

....

A nice lead-in for why wrapping the cell reference in TRIM would avoid
problems when there stray trailing spaces. If that were the case, your
replace command would effective clear the cell's contents.

 

[T. Valko]

...

Because $1:$65536 is the only range reference that's completely unaffected
by inserting or deleting rows/columns.

....

I was thinking that might be your reason.

...

The entire range.

....
Ok. Thanks, Harlan.