Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
Do a Google search on this group for "extract last name" - you'll get
plenty of solutions to this problem. Hope this helps. Pete On Jul 4, 7:32 pm, " wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
On Jul 4, 7:32 pm, "
wrote: 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". On Jul 4, 11:46 am, Pete_UK wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
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 " wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
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 On Jul 4, 8:11 pm, " wrote: On Jul 4, 7:32 pm, " wrote: 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". On Jul 4, 11:46 am, Pete_UK wrote: 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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
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". 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) 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. 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
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 Coderre" wrote: 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 " wrote: 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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
On Wed, 04 Jul 2007 11:32:32 -0700, "
wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
On Jul 4, 11:32 am, "
wrote: 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: (Assuming that "*" is part of the text in A1.) 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 signatu "Please insert the word 'not' wherever you think I omitted it inadvertently" ;-). Thanks to all for understanding my intended meaning. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
"Rick Rothstein (MVP - VB)" wrote...
.... 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... .... ? 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. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
wrote...
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) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
"Harlan Grove" wrote in message
... wrote... 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. -- Biff Microsoft Excel MVP |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
Another way: copy column then Edit Replace "* " with blank
On 4 Jul, 19:32, " wrote: 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. |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
"T. Valko" wrote...
"Harlan Grove" wrote in message .... =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? .... 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:$65 536,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:$65 536,N,1)) |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
"Lori" wrote...
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. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to extract right-most word?
"Harlan Grove" wrote in message
... "T. Valko" wrote... "Harlan Grove" wrote in message ... =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? ... 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. 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. .... Ok. Thanks, Harlan. -- Biff Microsoft Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
extract word function | Excel Worksheet Functions | |||
Formula to extract a specific word from text string | Excel Worksheet Functions | |||
Extract MS Excel Data embedded in MS Word | Excel Discussion (Misc queries) | |||
Extract data (not in table) from Word to Excel | Excel Discussion (Misc queries) | |||
Extract the first word from a cell? | Excel Discussion (Misc queries) |