Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return position of 2nd, 3rd, ect occurrence of a character in a st
the Excel =FIND command returns the position of the first instance of a
string that it finds. Is there any way to find position of the 2nd, 3rd, or nth occurrence? Using =MID is too messy. |
#2
|
|||
|
|||
Answer: Return position of 2nd, 3rd, ect occurrence of a character in a st
Yes, there is a way to find the position of the 2nd, 3rd, or nth occurrence of a character in a string in Excel.
You can use a combination of the FIND and SUBSTITUTE functions to achieve this. Here's an example of how to find the position of the 2nd occurrence of the letter "e" in the string "Excel is awesome":
This will return the position of the second occurrence of "e" in the string "Excel is awesome". You can modify the formula to find the position of the 3rd, 4th, or nth occurrence by adjusting the number in the SUBSTITUTE function to replace the appropriate occurrence of the character.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return position of 2nd, 3rd, ect occurrence of a character in a st
You could nest the find commands, using the result of the first, plus 1, as
the starting point for the next: =FIND("a",A1,FIND("a",A1)+1) finds the second "a" within cell A2, for instance. =FIND("a",A1,FIND("a",A1,FIND("a",A1)+1)+1) finds the third. Of course, this is subject to Excel's limit of seven levels of nesting. "jheby" wrote: the Excel =FIND command returns the position of the first instance of a string that it finds. Is there any way to find position of the 2nd, 3rd, or nth occurrence? Using =MID is too messy. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return position of 2nd, 3rd, ect occurrence of a character in a st
Try something like this:
For a text value in A1 B1: =FIND(CHAR(7),SUBSTITUTE(A1,"a",CHAR(7),3)) That formula finds the position of the 3rd instance of the letter "a" in Cell A1 Note: Char(7) is ASCII for the Bell sound...unlikely that it's in your string, right? Does that help? *********** Regards, Ron XL2002, WinXP-Pro "jheby" wrote: the Excel =FIND command returns the position of the first instance of a string that it finds. Is there any way to find position of the 2nd, 3rd, or nth occurrence? Using =MID is too messy. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return position of 2nd, 3rd, ect occurrence of a character in a st
Hi!
Try this: =FIND("~",SUBSTITUTE(A1,"x","~",B1)) Searching for "x". B1 holds the instance you want. Note: Substitute is case sensitive. To make it a little more robust: =FIND("~",SUBSTITUTE(UPPER(A1),"X","~",B1)) "~" is used as a "marker". The marker needs to be some char or group of chars that are more than likely not to appear in the string. Biff "jheby" wrote in message ... the Excel =FIND command returns the position of the first instance of a string that it finds. Is there any way to find position of the 2nd, 3rd, or nth occurrence? Using =MID is too messy. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return position of 2nd, 3rd, ect occurrence of a character in a st
On Tue, 14 Feb 2006 11:12:26 -0800, "jheby"
wrote: the Excel =FIND command returns the position of the first instance of a string that it finds. Is there any way to find position of the 2nd, 3rd, or nth occurrence? Using =MID is too messy. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr Then use the formula: =REGEX.FIND(StringToSearch, StringToFind, n) where 'n' is the instance number of the string. For a case-INsensitive version: =REGEX.FIND(StringToSearch, StringToFind, n, FALSE) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return cell position | Excel Worksheet Functions | |||
Count occurrence of character within a cell | Excel Discussion (Misc queries) | |||
Match Each Numeric occurrence and Return Individual Rows of Data | Excel Worksheet Functions | |||
How to remove or replace a carriage return character in a cell? | Excel Discussion (Misc queries) | |||
Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions |