| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last word in a string
Usually in a list of names, I frequently need to find the last word in a field, i.e., locate the last space. Is there any way of doing this with formulas alone or must I resort to VBA? -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last word in a string
=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ",
"^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) clearwaterdave wrote: Usually in a list of names, I frequently need to find the last word in a field, i.e., locate the last space. Is there any way of doing this with formulas alone or must I resort to VBA? -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last word in a string
=MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5),"
","·",LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256) -- Don Guillett SalesAid Software "clearwaterdave" <clearwaterdave.2bmhvt_1154035508.7878@excelforu m-nospam.com wrote in message news:clearwaterdave.2bmhvt_1154035508.7878@excelfo rum-nospam.com... Usually in a list of names, I frequently need to find the last word in a field, i.e., locate the last space. Is there any way of doing this with formulas alone or must I resort to VBA? -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last word in a string
Don Guillett Wrote: =MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5)," ","·",LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256) -- Don Guillett SalesAid Software "clearwaterdave" <clearwaterdave.2bmhvt_1154035508.7878@excelforu m-nospam.com wrote in message news:clearwaterdave.2bmhvt_1154035508.7878@excelfo rum-nospam.com... Usually in a list of names, I frequently need to find the last word in a field, i.e., locate the last space. Is there any way of doing this with formulas alone or must I resort to VBA? -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 I have thoroughly digested this piece of art and understand how it works -- ALMOST. Does anyone know the philosophy of how it works. Using the string "Bill & Susan Vilbert", a 20 character string I have tried to understand its workings. The second parm of the FIND field seems to find the location of the last space but I don't understand how. The first SUBSTITUTE assigns a "." to a location definded as the string length minus the length of the string without any spaces. By my calculations that would be 20 (string length) less 17 (string length without spaces) giving 3. That would mean that the space located at position 3 would be replaced by a period. In reality it is replacing the space at position 13 with a peiod. Since it is finding the right position and it is working, I have no problem. It's just a slight mental block I have as to how it's finding the 13th and not 3rd position. What am I overlooking? BY THE WAY Many, many thanks to all who helped me with this challenge. -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last word in a string
I like this formula (copied from Peo Sjoblom, IIRC) since the ^^ characters
don't usually appear in cells. Dots/periods appear much more often. =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ", "^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) Len(a1)-LEN(SUBSTITUTE(A1," ","")) Will tell you how many spaces are in A1. Say a1 contains: Bill & Susan Vilbert =len(a1) returns 20 =LEN(SUBSTITUTE(A1," ","")) returns 17 doing the subtraction returns 3. So now the formula looks like: =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",3))) This portion: SUBSTITUTE(A1," ", "^^",3) returns: "Bill & Susan^^Vilbert" It replaced that 3rd (aka last) occurance with ^^. The Find() will return 13 so the formula looks like: =RIGHT(A1,LEN(A1)-13) ======= Depending on the version of excel you're using, you can use: Select the cell with the formula tools|formula auditing|Evaluate formula And step through each piece of the formula. (IIRC, xl2002 added this.) In earlier versions, you can highlight portions of the formula (in the formula bar) and hit F9 to see how it evaluates. Hit ctrl-z to revert or edit|undo after you've changed it too much. clearwaterdave wrote: Don Guillett Wrote: =MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5)," ","·",LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256) -- Don Guillett SalesAid Software "clearwaterdave" <clearwaterdave.2bmhvt_1154035508.7878@excelforu m-nospam.com wrote in message news:clearwaterdave.2bmhvt_1154035508.7878@excelfo rum-nospam.com... Usually in a list of names, I frequently need to find the last word in a field, i.e., locate the last space. Is there any way of doing this with formulas alone or must I resort to VBA? -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 I have thoroughly digested this piece of art and understand how it works -- ALMOST. Does anyone know the philosophy of how it works. Using the string "Bill & Susan Vilbert", a 20 character string I have tried to understand its workings. The second parm of the FIND field seems to find the location of the last space but I don't understand how. The first SUBSTITUTE assigns a "." to a location definded as the string length minus the length of the string without any spaces. By my calculations that would be 20 (string length) less 17 (string length without spaces) giving 3. That would mean that the space located at position 3 would be replaced by a period. In reality it is replacing the space at position 13 with a peiod. Since it is finding the right position and it is working, I have no problem. It's just a slight mental block I have as to how it's finding the 13th and not 3rd position. What am I overlooking? BY THE WAY Many, many thanks to all who helped me with this challenge. -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last word in a string
Taking nothing away from Peo, I do believe this was originated by Leo Heuser
around the turn of the century. This sticks in memory because Leo's original formula used the @ to denote the indeterminate character, and this caused a lot of false links to appear in formulas. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Dave Peterson" wrote in message ... I like this formula (copied from Peo Sjoblom, IIRC) since the ^^ characters don't usually appear in cells. Dots/periods appear much more often. =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ", "^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) Len(a1)-LEN(SUBSTITUTE(A1," ","")) Will tell you how many spaces are in A1. Say a1 contains: Bill & Susan Vilbert =len(a1) returns 20 =LEN(SUBSTITUTE(A1," ","")) returns 17 doing the subtraction returns 3. So now the formula looks like: =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",3))) This portion: SUBSTITUTE(A1," ", "^^",3) returns: "Bill & Susan^^Vilbert" It replaced that 3rd (aka last) occurance with ^^. The Find() will return 13 so the formula looks like: =RIGHT(A1,LEN(A1)-13) ======= Depending on the version of excel you're using, you can use: Select the cell with the formula tools|formula auditing|Evaluate formula And step through each piece of the formula. (IIRC, xl2002 added this.) In earlier versions, you can highlight portions of the formula (in the formula bar) and hit F9 to see how it evaluates. Hit ctrl-z to revert or edit|undo after you've changed it too much. clearwaterdave wrote: Don Guillett Wrote: =MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5)," ","·",LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256) -- Don Guillett SalesAid Software "clearwaterdave" <clearwaterdave.2bmhvt_1154035508.7878@excelforu m-nospam.com wrote in message news:clearwaterdave.2bmhvt_1154035508.7878@excelfo rum-nospam.com... Usually in a list of names, I frequently need to find the last word in a field, i.e., locate the last space. Is there any way of doing this with formulas alone or must I resort to VBA? -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 I have thoroughly digested this piece of art and understand how it works -- ALMOST. Does anyone know the philosophy of how it works. Using the string "Bill & Susan Vilbert", a 20 character string I have tried to understand its workings. The second parm of the FIND field seems to find the location of the last space but I don't understand how. The first SUBSTITUTE assigns a "." to a location definded as the string length minus the length of the string without any spaces. By my calculations that would be 20 (string length) less 17 (string length without spaces) giving 3. That would mean that the space located at position 3 would be replaced by a period. In reality it is replacing the space at position 13 with a peiod. Since it is finding the right position and it is working, I have no problem. It's just a slight mental block I have as to how it's finding the 13th and not 3rd position. What am I overlooking? BY THE WAY Many, many thanks to all who helped me with this challenge. -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last word in a string
Could be....
I blame Peo for not reminding me <vbg. RagDyeR wrote: Taking nothing away from Peo, I do believe this was originated by Leo Heuser around the turn of the century. This sticks in memory because Leo's original formula used the @ to denote the indeterminate character, and this caused a lot of false links to appear in formulas. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Dave Peterson" wrote in message ... I like this formula (copied from Peo Sjoblom, IIRC) since the ^^ characters don't usually appear in cells. Dots/periods appear much more often. =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ", "^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) Len(a1)-LEN(SUBSTITUTE(A1," ","")) Will tell you how many spaces are in A1. Say a1 contains: Bill & Susan Vilbert =len(a1) returns 20 =LEN(SUBSTITUTE(A1," ","")) returns 17 doing the subtraction returns 3. So now the formula looks like: =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",3))) This portion: SUBSTITUTE(A1," ", "^^",3) returns: "Bill & Susan^^Vilbert" It replaced that 3rd (aka last) occurance with ^^. The Find() will return 13 so the formula looks like: =RIGHT(A1,LEN(A1)-13) ======= Depending on the version of excel you're using, you can use: Select the cell with the formula tools|formula auditing|Evaluate formula And step through each piece of the formula. (IIRC, xl2002 added this.) In earlier versions, you can highlight portions of the formula (in the formula bar) and hit F9 to see how it evaluates. Hit ctrl-z to revert or edit|undo after you've changed it too much. clearwaterdave wrote: Don Guillett Wrote: =MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5)," ","·",LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256) -- Don Guillett SalesAid Software "clearwaterdave" <clearwaterdave.2bmhvt_1154035508.7878@excelforu m-nospam.com wrote in message news:clearwaterdave.2bmhvt_1154035508.7878@excelfo rum-nospam.com... Usually in a list of names, I frequently need to find the last word in a field, i.e., locate the last space. Is there any way of doing this with formulas alone or must I resort to VBA? -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 I have thoroughly digested this piece of art and understand how it works -- ALMOST. Does anyone know the philosophy of how it works. Using the string "Bill & Susan Vilbert", a 20 character string I have tried to understand its workings. The second parm of the FIND field seems to find the location of the last space but I don't understand how. The first SUBSTITUTE assigns a "." to a location definded as the string length minus the length of the string without any spaces. By my calculations that would be 20 (string length) less 17 (string length without spaces) giving 3. That would mean that the space located at position 3 would be replaced by a period. In reality it is replacing the space at position 13 with a peiod. Since it is finding the right position and it is working, I have no problem. It's just a slight mental block I have as to how it's finding the 13th and not 3rd position. What am I overlooking? BY THE WAY Many, many thanks to all who helped me with this challenge. -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last word in a string
This array formula eliminates the need for "marker characters":
=MID(A1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) =CHAR(32))*(ROW(INDIRECT("1:"&LEN(A1)))))+1,255) My preference would be to use the "marker" method, though. It's not volatile, isn't an array and is a few keystrokes shorter. Biff "RagDyeR" wrote in message ... Taking nothing away from Peo, I do believe this was originated by Leo Heuser around the turn of the century. This sticks in memory because Leo's original formula used the @ to denote the indeterminate character, and this caused a lot of false links to appear in formulas. -- Regards, RD ---------------------------------------------------------------------------- ------------------- Please keep all correspondence within the Group, so all may benefit ! ---------------------------------------------------------------------------- ------------------- "Dave Peterson" wrote in message ... I like this formula (copied from Peo Sjoblom, IIRC) since the ^^ characters don't usually appear in cells. Dots/periods appear much more often. =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ", "^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) Len(a1)-LEN(SUBSTITUTE(A1," ","")) Will tell you how many spaces are in A1. Say a1 contains: Bill & Susan Vilbert =len(a1) returns 20 =LEN(SUBSTITUTE(A1," ","")) returns 17 doing the subtraction returns 3. So now the formula looks like: =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",3))) This portion: SUBSTITUTE(A1," ", "^^",3) returns: "Bill & Susan^^Vilbert" It replaced that 3rd (aka last) occurance with ^^. The Find() will return 13 so the formula looks like: =RIGHT(A1,LEN(A1)-13) ======= Depending on the version of excel you're using, you can use: Select the cell with the formula tools|formula auditing|Evaluate formula And step through each piece of the formula. (IIRC, xl2002 added this.) In earlier versions, you can highlight portions of the formula (in the formula bar) and hit F9 to see how it evaluates. Hit ctrl-z to revert or edit|undo after you've changed it too much. clearwaterdave wrote: Don Guillett Wrote: =MID(TRIM(G5),FIND("·",SUBSTITUTE(TRIM(G5)," ","·",LEN(TRIM(G5))-LEN(SUBSTITUTE(G5," ",""))))+1,256) -- Don Guillett SalesAid Software "clearwaterdave" <clearwaterdave.2bmhvt_1154035508.7878@excelforu m-nospam.com wrote in message news:clearwaterdave.2bmhvt_1154035508.7878@excelfo rum-nospam.com... Usually in a list of names, I frequently need to find the last word in a field, i.e., locate the last space. Is there any way of doing this with formulas alone or must I resort to VBA? -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 I have thoroughly digested this piece of art and understand how it works -- ALMOST. Does anyone know the philosophy of how it works. Using the string "Bill & Susan Vilbert", a 20 character string I have tried to understand its workings. The second parm of the FIND field seems to find the location of the last space but I don't understand how. The first SUBSTITUTE assigns a "." to a location definded as the string length minus the length of the string without any spaces. By my calculations that would be 20 (string length) less 17 (string length without spaces) giving 3. That would mean that the space located at position 3 would be replaced by a period. In reality it is replacing the space at position 13 with a peiod. Since it is finding the right position and it is working, I have no problem. It's just a slight mental block I have as to how it's finding the 13th and not 3rd position. What am I overlooking? BY THE WAY Many, many thanks to all who helped me with this challenge. -- clearwaterdave ------------------------------------------------------------------------ clearwaterdave's Profile: http://www.excelforum.com/member.php...o&userid=19365 View this thread: http://www.excelforum.com/showthread...hreadid=565801 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel -find records which DO NOT contain a particular text string | Excel Discussion (Misc queries) | |||
| Word could not parse your query options into a valid sql string | Excel Discussion (Misc queries) | |||
| Find 2nd instance of a word in a range. | Excel Worksheet Functions | |||
| How can I use find and replace to delete a word in Excel? | Excel Discussion (Misc queries) | |||
| How to find if a string starts with a digit | Excel Worksheet Functions | |||
Linear Mode
