Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |