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.

MSExcel: How to find the position of a character in a string? : Strings containing separation by slash character

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”)

MSExcel: How to find the position of a character in a string? : Finding the position of a character in a string using function FIND

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)

MSExcel: How to find the position of a character in a string? : Extract substring starting at given character with functions FIND and MID

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)

MSExcel: How to find the position of a character in a string? : Extract text before character with functions FIND and LEFT

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?

MSExcel: How to find the position of a character in a string? : Find nth occurrence of character in string excel with functions FIND and SUBSTITUTE

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