If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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 > > . > > 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
Find first character in cell  mahmad  Excel Worksheet Functions  10  October 2nd 08 05:03 PM 
Find the next occurance  Lou  Excel Worksheet Functions  6  April 8th 08 04:37 AM 
find a cell with a character  Dan Kowalski  Excel Worksheet Functions  1  February 28th 08 05:00 PM 
Find the 1st occurance of a number in a cell  lovemuch  Excel Worksheet Functions  4  August 17th 06 01:02 AM 
Find last occurance of character in text string  JDay01  Excel Worksheet Functions  2  February 14th 06 04:29 PM 