Home 
Search 
Today's Posts 
#1




find the last occurance of a character in a cell
I can use the Find function to find the 1st occurance of a charater in
a cell. But, how to i find the last occurence of a character. in this case, the i am looking for " ", or <space. The length of the string can vary, and the number of spaces can vary also. example: "Get dog food 55" There are 14 spaces (1+1+12). i am intersted in the location of the last, or 14th, space, the one that precedes the number 5 Thanks, Tonso 
#2




find the last occurance of a character in a cell
Look in vba help index for INSTRREV  Don Guillett Microsoft MVP Excel SalesAid Software "Tonso" wrote in message ... I can use the Find function to find the 1st occurance of a charater in a cell. But, how to i find the last occurence of a character. in this case, the i am looking for " ", or <space. The length of the string can vary, and the number of spaces can vary also. example: "Get dog food 55" There are 14 spaces (1+1+12). i am intersted in the location of the last, or 14th, space, the one that precedes the number 5 Thanks, Tonso 
#3




find the last occurance of a character in a cell
Try this...
All on one line. =FIND("^^",SUBSTITUTE(A1," ", "^^",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))  Biff Microsoft Excel MVP "Tonso" wrote in message ... I can use the Find function to find the 1st occurance of a charater in a cell. But, how to i find the last occurence of a character. in this case, the i am looking for " ", or <space. The length of the string can vary, and the number of spaces can vary also. example: "Get dog food 55" There are 14 spaces (1+1+12). i am intersted in the location of the last, or 14th, space, the one that precedes the number 5 Thanks, Tonso 
#4




find the last occurance of a character in a cell
On Apr 3, 4:28*pm, "T. Valko" wrote:
Try this... All on one line. =FIND("^^",SUBSTITUTE(A1," ", "^^",LEN(A1)LEN(SUBSTITUTE(A1," ",""))))  Biff Microsoft Excel MVP "Tonso" wrote in message ... I can use the Find function to find the 1st occurance of a charater in a cell. But, how to i find the last occurence of a character. in this case, the i am looking for " ", or <space. The length of the string can vary, and the number of spaces can vary also. example: "Get dog food * * * * * *55" There are 14 spaces (1+1+12). i am intersted in the location of the last, or 14th, space, the one that precedes the number 5 Thanks, Tonso Hide quoted text   Show quoted text  Don, Biff, Paul, Thank you all so very much! I do not know what I would do without this group. You all increase the value of Excel many times over!! Thansk again, Tonso 
#5




find the last occurance of a character in a cell
Hi,
You posted in programming but I think this is a worksheet method your after. The ARRAY formula returns the position of that last space in a string so for the character after you would add +1 at the end =MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=" ")*ROW(INDIRECT("A1:A"&LEN(A1)))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter.  Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Tonso" wrote: I can use the Find function to find the 1st occurance of a charater in a cell. But, how to i find the last occurence of a character. in this case, the i am looking for " ", or <space. The length of the string can vary, and the number of spaces can vary also. example: "Get dog food 55" There are 14 spaces (1+1+12). i am intersted in the location of the last, or 14th, space, the one that precedes the number 5 Thanks, Tonso . 
#6




find the last occurance of a character in a cell
You posted in programming
No you didn't, I had an elderly moment  Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Mike H" wrote: Hi, You posted in programming but I think this is a worksheet method your after. The ARRAY formula returns the position of that last space in a string so for the character after you would add +1 at the end =MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=" ")*ROW(INDIRECT("A1:A"&LEN(A1)))) This is an array formula which must be entered by pressing CTRL+Shift+Enter 'and not just Enter. If you do it correctly then Excel will put curly brackets 'around the formula {}. You can't type these yourself. If you edit the formula 'you must enter it again with CTRL+Shift+Enter.  Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Tonso" wrote: I can use the Find function to find the 1st occurance of a charater in a cell. But, how to i find the last occurence of a character. in this case, the i am looking for " ", or <space. The length of the string can vary, and the number of spaces can vary also. example: "Get dog food 55" There are 14 spaces (1+1+12). i am intersted in the location of the last, or 14th, space, the one that precedes the number 5 Thanks, Tonso . 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Find first character in cell  Excel Worksheet Functions  
Find the next occurance  Excel Worksheet Functions  
find a cell with a character  Excel Worksheet Functions  
Find the 1st occurance of a number in a cell  Excel Worksheet Functions  
Find last occurance of character in text string  Excel Worksheet Functions 