Find the Position of a Character in a String in Excel

When working with Excel, you may want to determine the position of a character within a string for various reasons.

For example, if you need to split a string into multiple cells using functions like LEFT, RIGHT, or MID, you must locate the position of delimiters such as semicolons, slashes, or commas.

In this tutorial, you will learn four techniques to locate the position of a character within a string in Excel.

Method 1: Use the FIND Function to Find the Position of the First Occurrence of a Character in a String

If you need to locate the position of the first occurrence of a particular character within a text string in Excel, you can utilize the FIND function.

The FIND function is case-sensitive and returns the starting position of one text string within another.

Let’s look at the following dataset showing the location of various video files:

We want to use the FIND function to determine the position of the backslash character in the various locations.

Additionally, we want to use the results returned by the FIND function inside the MID function to extract the titles of the videos to the right of the backslashes and display them in column B.

We use the following steps:

1. Select cell B2 and type in the following formula:

=MID(A2,FIND("\",A2)+1,1000)

2. Drag or double-click the fill handle feature in cell B2 to copy the formula down the column.

The formula extracts the video file titles on the right of the first occurrence of the backslash characters in the strings in column A and displays them in column B.

Explanation of the Formula

=MID(A2,FIND(“\”,A2)+1,1000)

This formula extracts all the text from cell A2 that appears after the first occurrence of the backslash () character.

Let’s break it down:

1. FIND(“\”,A2): The FIND function in the formula returns the first position of the backslash in the text as depicted below:

Note: The FIND function is case-sensitive, meaning that if, for example, you look for the position of an uppercase letter “F” in a string, it will not consider the occurrence of a lowercase “f.” If you want to determine the position of a character without considering its case, use the SEARCH function instead.

2. FIND(“\”,A2)+1: Since we want to extract the text after the backslash, we add 1 to the result of the FIND function to move the starting position of the extraction one character after the backslash.
3. 1000: This specifies the number of characters we want to extract. In this case, it is set to 1000, which is a large value likely to exceed the length of the text in cell A2. The purpose of using a large value is to ensure that the formula extracts all the remaining text from the starting position until the end of the text.
4. MID(A2, FIND(“\”,A2)+1, 1000): The MID function is used to extract a specific number of characters from a text string. It takes three arguments: the text string (A2), the starting position (FIND(“”,A2)+1), and the number of characters to extract (1000). This function returns the extracted portion of the text.

Note: This technique is most suitable for scenarios where there is only a single instance of the character, the position of which you desire to ascertain in each text string.

Also read: How to Add Text to the Beginning or End of All Cells in Excel

How to Extract Text Before the Backslash Character

If we want to extract the text before the backslash character, we can use the following formula:

=LEFT(A2,FIND("\",A2)-1)

By utilizing the FIND function in the formula, the initial position of the backslash in the string is identified.

Subsequently, the LEFT function uses the determined position minus one to extract the substring preceding the backslash character.

Also read: Find the Last Space in Text String in Excel

Method 2: Use the FIND and SUBSTITUTE Functions to Find N-th Occurrence of Character’s Position in a String

Suppose you need to locate, for example, the position of the second occurrence of a particular character within a string in Excel.

In that case, you can utilize a formula that combines the FIND and SUBSTITUTE functions.

Suppose we have the following dataset showing the full paths of various video files:

Notice that the path of each video file has two instances of the backslash () character.

We want to use a formula that combines the FIND and SUBSTITUTE functions to find the position of the second occurrence of the backslash character in the text strings.

Additionally, we will use the MID function to extract the names of the video files and display them in column B.

We use the below steps:

1. Select cell B2 and type in the formula below:

=MID(A2,FIND("#",SUBSTITUTE(A2,"","#",2))+1,1000)

2. Double-click or drag the fill handle feature in cell B2 to copy the formula down the column.

The formula extracts the names of the video files to the right of the second backslash characters in the paths and displays them in column B.

Explanation of the Formula

=MID(A2,FIND(“#”,SUBSTITUTE(A2,””,”#”,2))+1,1000)

This formula extracts a substring, to the maximum of 1000 characters, from the text string in cell A2 starting at the character immediately after the second occurrence of the backslash () symbol.

We break down the formula:

1. SUBSTITUTE(A2, “”, “#”, 2): The SUBSTITUTE function replaces the second occurrence of the backslash (“”) symbol in cell A2 with a unique character (in this case, “#”). The “#” symbol replaces the second backslash symbol in the resulting text.

Note: The value 2 in the SUBSTITUTE function is the instance-num argument. If you want to find the position of a different occurrence of the character, for example, the third occurrence, change the instance-num argument in the SUBSTITUTE function accordingly.

2. FIND(“#”, SUBSTITUTE(A2, “”, “#”, 2)): The FIND function is then used to locate the position of the “#” symbol within the modified text.
3. MID(A2, FIND(“#”, SUBSTITUTE(A2, “”, “#”, 2)) + 1, 1000): Finally, the MID function is applied to the original text in cell A2 to extract a substring starting from the character after the second backslash. The second argument is the starting position, obtained by adding 1 to the position of the “#” symbol.

The third argument, 1000, specifies the maximum number of characters to extract. The purpose of using a large value is to ensure that the formula extracts all the remaining text from the starting position until the end of the text.

Note: This technique is most suitable for scenarios where a fixed number of instances of the character exists in text strings, the position of which you desire to ascertain.

Also read: Find the Closest Match in Excel (Nearest Value)

Method 3: Using LEN, FIND, and SUBSTITUTE Functions to Find Last Occurrence of a Character’s Position

Suppose you need to locate the position of the last occurrence of a particular character within a text string in Excel.

In that case, you can utilize a formula that combines the LEN, FIND, and SUBSTITUTE functions.

Assume we have the following dataset of a list of paths of various video files:

Notice that the paths have varying numbers of backslashes.

We aim to apply the formula that combines the LEN, FIND, and SUBSTITUTE functions to find the last occurrence of the backslash symbol in each text string in column A.

Additionally, we will use the RIGHT function to extract the names of the video files on the right of the last backslash characters in the paths.

We use the following steps:

1. Select cell B2 and type in the below formula:

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2,"","#",LEN(A2)-LEN(SUBSTITUTE(A2,"\",""))),1))

2. Drag or double-click the fill handle feature in cell B2 to copy the formula down the column.

The formula has extracted the names of the video files on the right of the last backslash characters in the paths in column A and displayed them in column C.

Explanation of the formula

=RIGHT(A2,LEN(A2)-FIND(“#”,SUBSTITUTE(A2,””,”#”,LEN(A2)-LEN(SUBSTITUTE(A2,”\”,””))),1))

This formula finds the position of the last occurrence of the backslash character in the text string in cell A2.

It then uses the RIGHT function to extract the substring to the right of the last backslash.

Let’s break down the formula:

1. LEN(SUBSTITUTE(A2,”\”,””)): This segment of the formula uses the SUBSTITUTE function to replace all the backslash () characters in the text string in cell A2 with empty strings. Subsequently, the LEN function returns the number of characters in the modified string. The operation is equivalent to computing the number of characters of the text string in cell A2 minus the backslash characters.
2. LEN(A2)-LEN(SUBSTITUTE(A2,”\”,””)): This part of the formula subtracts the number of characters in the text string in cell A2 minus backslash symbols from the number of characters in the text string in cell A2 with backslash symbols. The result equals the number of backslash symbols in the text string.
3. SUBSTITUTE(A2,””,”#”,LEN(A2)-LEN(SUBSTITUTE(A2,”\”,””))): This segment of the formula replaces the last backslash symbol in the text string with a unique character, in this case, a “#” symbol.
4. FIND(“#”,SUBSTITUTE(A2,””,”#”,LEN(A2)-LEN(SUBSTITUTE(A2,”\”,””))),1): This part of the formula returns the position of the “#” symbol in the modified text string. The returned result equals the position of the last backslash symbol in the original text string in cell A2.
5. LEN(A2)-FIND(“#”,SUBSTITUTE(A2,””,”#”,LEN(A2)-LEN(SUBSTITUTE(A2,”\”,””))),1): This part of the formula takes away the position of the last backslash in the text string in cell A2 from the number of characters in the entire text string in the cell. The result equals the number of characters in the substring to the right of the last backslash in the text string.
6. =RIGHT(A2,LEN(A2)-FIND(“#”,SUBSTITUTE(A2,””,”#”,LEN(A2)-LEN(SUBSTITUTE(A2,”\”,””))),1)): Finally, the RIGHT function returns the substring to the right of the last backslash symbol in the text string in cell A2.

Note: This method is effective, but it isn’t straightforward. If you are conversant with Excel VBA, the following technique that applies a user-defined function is an easier alternative.

Also read: Extract Number from Text in Excel (Beginning, End, or Middle)

Method 4: Using User-Defined Function to Find the Position of Last Occurrence of a Character in String

We can create a user-defined function in Excel VBA and then use it in a formula to find the position of the last occurrence of a character in a string in Excel.

Suppose we have the following list of paths of video files:

Notice that the paths have varying numbers of backslash symbols.

We want to create a user-defined function in Excel VBA and then use it in a formula to return the last occurrence of the backslash symbols in the text strings and extract the substrings to the right of the backslash characters.

We proceed as follows:

1. Press the shortcut Alt + F11 to open the VB Editor.
2. Open the Insert menu on the menu bar and choose Module to insert a module.

3. Copy the following function procedure and paste it into the module:

'Code developed by Steve Scott from https://spreadsheetplanet.com
Function FINALPOSITION(tCell As Range, tChar As String)
    Dim tLen As Integer
    tLen = Len(tCell)
    For i = tLen To 1 Step -1
        If Mid(tCell, i - 1, 1) = tChar Then
            FINALPOSITION = i
            Exit Function
        End If
    Next i
End Function

4. Save the workbook as a Macro-Enabled Workbook.
5. Press Alt + F11 to switch to the current worksheet.
6. Select cell B2 on the dataset and type in the formula below:

=RIGHT(A2,LEN(A2)-FINALPOSITION(A2,"\")+1)

Notice that the formula includes the FINALPOSITION function we created in Excel VBA.

7. Drag or double-click the fill handle feature in cell B2 to copy the formula down the column.

The formula extracts the substrings to the right of the last backslash symbols in the text strings and displays them in column B.

Explanation of the User-Defined Function

The function finds the position of the last occurrence of a specific character (tChar) within a given cell (tCell) in a worksheet.

Here’s a breakdown of the function procedure:

1. Dim tLen As Integer: Declares a variable tLen of type Integer, which will be used to store the length of the text in the tCell.
2. tLen = Len(tCell): Assigns the length of the text in the tCell to the variable tLen using the Len function.
3. For i = tLen To 1 Step -1: Initiates a loop that starts from the length of the text (tLen) and decrements by 1 until reaching 1. This loop iterates through each character in the tCell from right to left.
4. If Mid(tCell, i – 1, 1) = tChar Then: Checks if the character at the current position (i – 1) within the tCell is equal to the tChar. The Mid function is used to extract a single character from the tCell.
5. FINALPOSITION = i: If the character matches tChar, the current position (i) is assigned to the function name “FINALPOSITION.” This means that the function will return this value as its result.
6. Exit Function: Exits the function immediately after finding the first occurrence of tChar in tCell. This is done to prevent unnecessary iterations and return the position of the last occurrence.

Explanation of the Formula

=RIGHT(A2,LEN(A2)-FINALPOSITION(A2,”\”)+1)

This formula extracts the substring on the right of the last backslash symbol in the text string in cell A2.

Here’s the breakdown of the formula:

1. LEN(A2): This part calculates the number of characters in the text string in cell A2.
2. FINALPOSITION(A2, “\”): The FINALPOSITION user-defined function determines the position of the last backslash in the text string.
3. LEN(A2) – FINALPOSITION(A2, “\”) + 1: This part subtracts the final position of the backslash from the length of the text and adds 1. Adding 1 is to include the backslash in the extracted portion.
4. RIGHT(A2, LEN(A2) – FINALPOSITION(A2, “\”) + 1): Finally, the RIGHT function extracts the number of characters equal to the value returned in step 3 from the right end of a text string.

This tutorial showed four techniques for finding the position of a character in a string in Excel. We hope you found the tutorial helpful.

Other Excel articles you may also like:

• How to Remove Text after a Specific Character in Excel?
• Separate Comma Separated Values in Excel
• Find Last Occurrence of a Value in a Column in Excel
• How to Extract First Name in Excel
• Extract Last Name in Excel
• SEARCH vs FIND Function in Excel