| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
find in reverse
I need to find the last occurrence of a character in a string.
Does anyone know of a way to make find() search from the end of a string, rather than from the beginning. Thank you Steven Wheeler |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
Try this...
Seems like it should be easier than this. x234x678x0 Find the position of the last instance of "x": =LOOKUP(1E100,SEARCH("x",MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))) -- Biff Microsoft Excel MVP "smw" wrote in message ... I need to find the last occurrence of a character in a string. Does anyone know of a way to make find() search from the end of a string, rather than from the beginning. Thank you Steven Wheeler |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
Ok, I knew it had to be "easier".
x234x678x0 Find the position of the last instance of "x": =SEARCH("^^",SUBSTITUTE(A1,"x","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))) Note that SUBSTITUTE is case sensitive. So X and x don't match! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Seems like it should be easier than this. x234x678x0 Find the position of the last instance of "x": =LOOKUP(1E100,SEARCH("x",MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))) -- Biff Microsoft Excel MVP "smw" wrote in message ... I need to find the last occurrence of a character in a string. Does anyone know of a way to make find() search from the end of a string, rather than from the beginning. Thank you Steven Wheeler |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
You can also do it with this array-entered** formula...
=MAX((MID(A1,ROW(1:15),1)="x")*ROW(1:15)) **commit formula using Ctrl+Shift+Enter, not just Enter by itself. Two side benefits of the above formula is that it returns 0 as the result if "x" is not in A1 (your formula returns a #VALUE! error) and it is case insensitive (it finds the last "x" or "X"). -- Rick (MVP - Excel) "T. Valko" wrote in message ... Ok, I knew it had to be "easier". x234x678x0 Find the position of the last instance of "x": =SEARCH("^^",SUBSTITUTE(A1,"x","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))) Note that SUBSTITUTE is case sensitive. So X and x don't match! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Seems like it should be easier than this. x234x678x0 Find the position of the last instance of "x": =LOOKUP(1E100,SEARCH("x",MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))) -- Biff Microsoft Excel MVP "smw" wrote in message ... I need to find the last occurrence of a character in a string. Does anyone know of a way to make find() search from the end of a string, rather than from the beginning. Thank you Steven Wheeler |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
Sorry, I meant to use 99, not 15 for the ROW array (still array-entered)...
=MAX((MID(A1,ROW(1:99),1)="x")*ROW(1:99)) I used 15 originally for debugging purposes and then forgot to reset it afterwards. For the OP... the formula (with the 99s) assumes your text in A1 will not be longer than 99 characters. If it could be, just change both 99s to a (same) value equal to or larger than the maximum number of characters that can appear in A1. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can also do it with this array-entered** formula... =MAX((MID(A1,ROW(1:15),1)="x")*ROW(1:15)) **commit formula using Ctrl+Shift+Enter, not just Enter by itself. Two side benefits of the above formula is that it returns 0 as the result if "x" is not in A1 (your formula returns a #VALUE! error) and it is case insensitive (it finds the last "x" or "X"). -- Rick (MVP - Excel) "T. Valko" wrote in message ... Ok, I knew it had to be "easier". x234x678x0 Find the position of the last instance of "x": =SEARCH("^^",SUBSTITUTE(A1,"x","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))) Note that SUBSTITUTE is case sensitive. So X and x don't match! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Seems like it should be easier than this. x234x678x0 Find the position of the last instance of "x": =LOOKUP(1E100,SEARCH("x",MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))) -- Biff Microsoft Excel MVP "smw" wrote in message ... I need to find the last occurrence of a character in a string. Does anyone know of a way to make find() search from the end of a string, rather than from the beginning. Thank you Steven Wheeler |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
Hi
I understand you are looking to find from the end of a string and not a range.. If you are looking to implement this in code then use the function InstrRev which will return the position of the occurence from end. OR If you are looking to use it as a function from worksheet you can copy the below to VBE and use this function from Insert|Function|UserDefined If this post helps click Yes --------------- Jacob Skaria Function FindStringfromRev(strString, strFindString) FindStringfromRev = InStrRev(strString, strFindString) End Function To copy this 'Launch VBE using short-key Alt+F11. On the left treeview right click 'This Workbook '. Insert a module and paste the below code. Save. Get back to Workbook. Set the Security level to low/medium in (Tools|Macro|Security). |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
I see I'm not having any "influence" on your use of expressions like:
ROW(1:99) <g -- Biff Microsoft Excel MVP "Rick Rothstein" wrote in message ... Sorry, I meant to use 99, not 15 for the ROW array (still array-entered)... =MAX((MID(A1,ROW(1:99),1)="x")*ROW(1:99)) I used 15 originally for debugging purposes and then forgot to reset it afterwards. For the OP... the formula (with the 99s) assumes your text in A1 will not be longer than 99 characters. If it could be, just change both 99s to a (same) value equal to or larger than the maximum number of characters that can appear in A1. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can also do it with this array-entered** formula... =MAX((MID(A1,ROW(1:15),1)="x")*ROW(1:15)) **commit formula using Ctrl+Shift+Enter, not just Enter by itself. Two side benefits of the above formula is that it returns 0 as the result if "x" is not in A1 (your formula returns a #VALUE! error) and it is case insensitive (it finds the last "x" or "X"). -- Rick (MVP - Excel) "T. Valko" wrote in message ... Ok, I knew it had to be "easier". x234x678x0 Find the position of the last instance of "x": =SEARCH("^^",SUBSTITUTE(A1,"x","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))) Note that SUBSTITUTE is case sensitive. So X and x don't match! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Seems like it should be easier than this. x234x678x0 Find the position of the last instance of "x": =LOOKUP(1E100,SEARCH("x",MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))) -- Biff Microsoft Excel MVP "smw" wrote in message ... I need to find the last occurrence of a character in a string. Does anyone know of a way to make find() search from the end of a string, rather than from the beginning. Thank you Steven Wheeler |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
I see I'm not having any "influence" on your use of expressions like:
ROW(1:99) <g It's nearly 3:00 AM here and I'm about to go to sleep... your reference escapes me at the moment... would you be so kind as to refresh my memory about you meant by that? -- Rick (MVP - Excel) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
Hi Rick,
Can this be done in VBA? For instance finding the last "\" in a path. I hacked this out. There has to be a more elegant way. Sub myTest() myPath = "c:\users\jaf\text.txt" n = 0 For i = 1 To Len(myPath) ctr1 = Mid(myPath, i, 1) last = InStr(1, ctr1, "\", vbTextCompare) If last = 1 Then n = n + 1 ctr2 = i End If Next myFilename = Mid(myPath, ctr2 + 1, Len(myPath) - ctr2) Debug.Print InStr(ctr2, myPath, "\"), myFilename End Sub John "Rick Rothstein" wrote in message ... Sorry, I meant to use 99, not 15 for the ROW array (still array-entered)... =MAX((MID(A1,ROW(1:99),1)="x")*ROW(1:99)) I used 15 originally for debugging purposes and then forgot to reset it afterwards. For the OP... the formula (with the 99s) assumes your text in A1 will not be longer than 99 characters. If it could be, just change both 99s to a (same) value equal to or larger than the maximum number of characters that can appear in A1. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can also do it with this array-entered** formula... =MAX((MID(A1,ROW(1:15),1)="x")*ROW(1:15)) **commit formula using Ctrl+Shift+Enter, not just Enter by itself. Two side benefits of the above formula is that it returns 0 as the result if "x" is not in A1 (your formula returns a #VALUE! error) and it is case insensitive (it finds the last "x" or "X"). -- Rick (MVP - Excel) "T. Valko" wrote in message ... Ok, I knew it had to be "easier". x234x678x0 Find the position of the last instance of "x": =SEARCH("^^",SUBSTITUTE(A1,"x","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))) Note that SUBSTITUTE is case sensitive. So X and x don't match! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Seems like it should be easier than this. x234x678x0 Find the position of the last instance of "x": =LOOKUP(1E100,SEARCH("x",MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))) -- Biff Microsoft Excel MVP "smw" wrote in message ... I need to find the last occurrence of a character in a string. Does anyone know of a way to make find() search from the end of a string, rather than from the beginning. Thank you Steven Wheeler |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
You can use the InStrRev function to search backwards through text. To get
the filename from a path... FileName = Mid(Path, InStrRev(Path, "\") + 1) -- Rick (MVP - Excel) "jaf" wrote in message ... Hi Rick, Can this be done in VBA? For instance finding the last "\" in a path. I hacked this out. There has to be a more elegant way. Sub myTest() myPath = "c:\users\jaf\text.txt" n = 0 For i = 1 To Len(myPath) ctr1 = Mid(myPath, i, 1) last = InStr(1, ctr1, "\", vbTextCompare) If last = 1 Then n = n + 1 ctr2 = i End If Next myFilename = Mid(myPath, ctr2 + 1, Len(myPath) - ctr2) Debug.Print InStr(ctr2, myPath, "\"), myFilename End Sub John "Rick Rothstein" wrote in message ... Sorry, I meant to use 99, not 15 for the ROW array (still array-entered)... =MAX((MID(A1,ROW(1:99),1)="x")*ROW(1:99)) I used 15 originally for debugging purposes and then forgot to reset it afterwards. For the OP... the formula (with the 99s) assumes your text in A1 will not be longer than 99 characters. If it could be, just change both 99s to a (same) value equal to or larger than the maximum number of characters that can appear in A1. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can also do it with this array-entered** formula... =MAX((MID(A1,ROW(1:15),1)="x")*ROW(1:15)) **commit formula using Ctrl+Shift+Enter, not just Enter by itself. Two side benefits of the above formula is that it returns 0 as the result if "x" is not in A1 (your formula returns a #VALUE! error) and it is case insensitive (it finds the last "x" or "X"). -- Rick (MVP - Excel) "T. Valko" wrote in message ... Ok, I knew it had to be "easier". x234x678x0 Find the position of the last instance of "x": =SEARCH("^^",SUBSTITUTE(A1,"x","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))) Note that SUBSTITUTE is case sensitive. So X and x don't match! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Seems like it should be easier than this. x234x678x0 Find the position of the last instance of "x": =LOOKUP(1E100,SEARCH("x",MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))) -- Biff Microsoft Excel MVP "smw" wrote in message ... I need to find the last occurrence of a character in a string. Does anyone know of a way to make find() search from the end of a string, rather than from the beginning. Thank you Steven Wheeler |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
find in reverse
Hi Rick,
Thanks. I've never new that one was there. John "Rick Rothstein" wrote in message ... You can use the InStrRev function to search backwards through text. To get the filename from a path... FileName = Mid(Path, InStrRev(Path, "\") + 1) -- Rick (MVP - Excel) "jaf" wrote in message ... Hi Rick, Can this be done in VBA? For instance finding the last "\" in a path. I hacked this out. There has to be a more elegant way. Sub myTest() myPath = "c:\users\jaf\text.txt" n = 0 For i = 1 To Len(myPath) ctr1 = Mid(myPath, i, 1) last = InStr(1, ctr1, "\", vbTextCompare) If last = 1 Then n = n + 1 ctr2 = i End If Next myFilename = Mid(myPath, ctr2 + 1, Len(myPath) - ctr2) Debug.Print InStr(ctr2, myPath, "\"), myFilename End Sub John "Rick Rothstein" wrote in message ... Sorry, I meant to use 99, not 15 for the ROW array (still array-entered)... =MAX((MID(A1,ROW(1:99),1)="x")*ROW(1:99)) I used 15 originally for debugging purposes and then forgot to reset it afterwards. For the OP... the formula (with the 99s) assumes your text in A1 will not be longer than 99 characters. If it could be, just change both 99s to a (same) value equal to or larger than the maximum number of characters that can appear in A1. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... You can also do it with this array-entered** formula... =MAX((MID(A1,ROW(1:15),1)="x")*ROW(1:15)) **commit formula using Ctrl+Shift+Enter, not just Enter by itself. Two side benefits of the above formula is that it returns 0 as the result if "x" is not in A1 (your formula returns a #VALUE! error) and it is case insensitive (it finds the last "x" or "X"). -- Rick (MVP - Excel) "T. Valko" wrote in message ... Ok, I knew it had to be "easier". x234x678x0 Find the position of the last instance of "x": =SEARCH("^^",SUBSTITUTE(A1,"x","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"x","")))) Note that SUBSTITUTE is case sensitive. So X and x don't match! -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Seems like it should be easier than this. x234x678x0 Find the position of the last instance of "x": =LOOKUP(1E100,SEARCH("x",MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1)))) -- Biff Microsoft Excel MVP "smw" wrote in message ... I need to find the last occurrence of a character in a string. Does anyone know of a way to make find() search from the end of a string, rather than from the beginning. Thank you Steven Wheeler |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reverse Find | Excel Discussion (Misc queries) | |||
| reverse value | Excel Worksheet Functions | |||
| reverse value | Excel Worksheet Functions | |||
| How do I do a Reverse Find/Search in Excel? | Excel Worksheet Functions | |||
| Reverse Sum | Excel Discussion (Misc queries) | |||
Linear Mode
