MSExcel: How to find the position of a character in a 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 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

Become an Excel Pro: Join Our Course!

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

Enroll Here

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

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

Frequently Asked Questions

What formula can be used in Excel to identify the position of a specific character within a text string, aiding in data manipulation and analysis?
Use the `FIND` or `SEARCH` function in Excel to find the position of a character within a string. The formula looks like `=FIND(character, cell_reference)` for case-sensitive search or `=SEARCH(character, cell_reference)` for case-insensitive search. Replace `character` with the character you're searching for and `cell_reference` with the cell containing the text.

Complete 2019 Excel for Beginners in video


Yoann Bierling
About the author - Yoann Bierling
Yoann Bierling is a Web Publishing & Digital Consulting professional, making a global impact through expertise and innovation in technologies. Passionate about empowering individuals and organizations to thrive in the digital age, he is driven to deliver exceptional results and drive growth through educational content creation.

Become an Excel Pro: Join Our Course!

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

Enroll Here

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.



Comments (0)

Leave a comment