How to find position of nth space in Excel string?
When working with text data in Excel, it's common to encounter cells containing multiple words separated by spaces. For example, you might need to find out where the first or nth space occurs in each cell so you can further process or extract information from your strings. Efficiently locating the position of a specific space character can help when splitting names, parsing codes, or organizing data. This guide outlines practical techniques to quickly find the position of the first, second, or any nth space in your cell content, as illustrated in the screenshot below. You’ll also discover how to clean up unwanted spaces and explore alternative solutions for flexible and advanced space location tasks.
Find the position of nth space with formula
Locate nth space with a more flexible Excel formula
Find the position of nth space with VBA macro
Remove leading/trailing/extra/all spaces from string
Find the position of nth space with formula
To determine the position of a specific space character within a text string, you can use built-in Excel functions. Each approach below can help you locate the 1st, 2nd, 3rd, or 4th space by nesting the FIND function. This method is suitable for cases where the number of spaces is limited and known in advance.
| Find position of first space | =FIND(" ",A1) |
| Find position of second space | =FIND(" ",A1,FIND(" ",A1)+1) |
| Find position of third space | =FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1) |
| Find position of forth space | =FIND(" ",A1,FIND(" ",A1,FIND(" ",A1, FIND(" ",A1)+1)+1)+1) |
For instance, if you want to find the position of the second space in each cell of your list, here’s how:
Select a blank cell, such as C2, and enter the following formula:
=FIND(" ",A2,FIND(" ",A2)+1)After entering the formula, press Enter. To apply this formula to other cells, drag the fill handle down alongside your list. This will automatically compute the position for each string. See screenshot:
Note: If there are fewer spaces than specified in a cell, the formula will return a #VALUE! error. Consider checking your data or using error-handling functions such as IFERROR for improved robustness.
This method is clear and quick for handling up to a small fixed number of spaces. For strings that require locating higher space positions or dynamic n-values, try a more flexible formula solution below.
Locate nth space with a more flexible Excel formula
If you need to find the position of the nth space in a text string, but want a formula that doesn’t require manually nesting multiple FIND functions, you can use SUBSTITUTE and FIND together. This approach allows you to adjust the target space position dynamically by changing a cell reference or variable and is useful for longer strings or modular Excel workflows.
For example, suppose the full text is in cell A2 and the space position you want to find (for example, the 3rd space) is in cell B2 (enter the desired n value such as 3 in B2):
=FIND("#",SUBSTITUTE(A2," ","#",B2))Enter this formula in cell C2. You can then copy it down to analyze other rows in your dataset. The formula replaces the nth space with the # character, then finds its position using the FIND function. This method is especially effective when the target space position varies between rows, or when working with more complex text strings.
Tips:
- This formula supports dynamic n-values, so you can reference different n (B2) for each row.
- If the value in B2 exceeds the number of spaces in the string, the result will be #VALUE!. You can wrap it with
IFERRORfor a more user-friendly display, such as=IFERROR(FIND("#",SUBSTITUTE(A2," ","#",B2)),"Not Found"). - Try changing the "#" character to another symbol if your actual text contains "#".
This technique is recommended when handling variable or larger n values and promotes efficiency for more complex data structures.
Find the position of nth space with VBA macro
In scenarios where you need maximum flexibility or want to automate the process for a large dataset, using a VBA macro is an excellent choice. With a VBA solution, you can loop through each character in a string, count spaces, and report the position of the nth occurrence. This works well even when dealing with varying text structures, long sentences, or dynamically changing required n-values.
1. Click Developer Tools > Visual Basic. When the Microsoft Visual Basic for Applications window opens, click Insert > Module and paste the following code into the Module:
Sub PositionOfNthSpace()
Dim txt As String
Dim spaceNum As Integer
Dim i As Integer
Dim cnt As Integer
Dim pos As Integer
On Error Resume Next
xTitleId = "KutoolsforExcel"
txt = Application.InputBox("Enter text to search:", xTitleId, "", Type:=2)
spaceNum = Application.InputBox("Find which space position (n):", xTitleId, 1, Type:=1)
pos = 0
cnt = 0
For i = 1 To Len(txt)
If Mid(txt, i, 1) = " " Then
cnt = cnt + 1
If cnt = spaceNum Then
pos = i
Exit For
End If
End If
Next i
If pos = 0 Then
MsgBox "The string does not contain " & spaceNum & " spaces.", vbExclamation, xTitleId
Else
MsgBox "The position of the " & spaceNum & "th space is: " & pos, vbInformation, xTitleId
End If
End Sub2. Click the 
run button to execute the macro. Two dialogs will prompt you: first to input your text string, and then to specify which space position to find (e.g., enter "3" for the 3rd space). The macro will display the character position if found, otherwise a notification will appear.
This VBA method is powerful when standard formulas are insufficient, or you need to batch process space positions in diverse string data.
Remove leading/trailing/extra/all spaces from string
Often, you may need to clean up unwanted spaces in your text data, such as leading, trailing, extra, or all spaces. Excel does not provide a direct built-in function for bulk space removal, but you can streamline the process quickly and accurately with Kutools for Excel – a feature-rich add-in. The Remove Spaces utility is especially useful for tidying up imported or poorly formatted datasets before you analyze or split text by space positions.
After free installing Kutools for Excel, please do as below:
1. Select the strings you want to remove spaces, then click Kutools > Text > Remove Spaces. See screenshot:
2. In the Remove Spaces dialog box, check the desired option under Spaces Type, such as Remove Leading Spaces, Remove Trailing Spaces, Remove Extra Spaces, or Remove All Spaces. You can preview the result in the Preview section before confirming. See screenshot:
3. Click Ok or Apply. The unwanted spaces will be removed instantly from your selected strings.
In summary, whether you're dealing with small lists or large datasets, you can choose from a variety of solutions—built-in formulas, flexible formula approaches, VBA macros, or add-in utilities—according to your needs. When the number of spaces is known and limited, nesting FIND works well; for larger or variable n values, try the SUBSTITUTE and FIND combination or leverage VBA for custom automation. Always ensure strings are cleaned of unwanted spaces beforehand for most reliable results.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in