MSExcel: How to find the position of a character in a string?
Excel find position of character in string
Finding the position of a character in a string can be a very simple operation in MSExcel, by using the corresponding built-in function “FIND”.
Excel find character position in string and extract substring
For example, let’s say that we have a list of directories and file names, and only want to get a final result with the file names.
In that case, even an advanced vlookup in Excel would not help, as it would not allow to extract any information from the main string.
The best solution is to use the function “FIND” to find the position of a character in the string, and use this number to extract the rest of the string using the “MID” function in Excel.
Find position of character in Excel string
To find the position of a specific given character in Excel, simply use the function “FIND” on the string, giving as parameters the character to find and the string in which to look for, for example in reference to another cell in the Excel spreadsheet.
'=FIND(char,”string”)
Extract string starting at character
Then, using the information of the character position, use the function MID to extract the required string starting after the character found by adding +1, and extracting the whole rest of the string by using a string length long enough, such as 999, to be sure to cover the whole result.
'=MID(“string”,”char”+1,999)
Excel extract text before character
To extract the text before a given character in Excel, simply find the character position, and use the “LEFT” function with one character less to extract the text before character that has been found.
'=LEFT(“string”,”char”-1)
Find nth occurrence of character in string excel
Finding the nth occurrence of character in string in Excel done by using both the functions “FIND” and “SUBSTITUTE”.
The “SUBSTITUTE” function will replace the requested nth instance of the researched character by a special one, and the function “FIND” will get the position of that special character in the string.
Simply selecting the nth occurrence to look for in the given string, and the result will be displayed showing the nth occurrence of character in the string.
'=FIND(CHAR(1),SUBSTITUTE(“string”,”char”,CHAR(1),nth))
How To Find Nth Occurrence (Position) Of A Character In Text String In Excel?
Excel find position of character in string from right
To find the position of a character in a string from the right, we will use a similar solutioj using “FIND” and “SUBSTITUTE” to find the last occurrence of a given character in a string.
With this solution, the last position of a character in a string, which is also the position of a character in a string from right, will be displayed.
'=FIND(CHAR(1),SUBSTITUTE(“string”,”char”,CHAR(1),LEN(A2)-LEN(SUBSTITUTE(“string”,”char”,))),1)
Find Position of the Last Occurrence of a Character in a String in Excel