# How to replace the nth occurrence of a character in Excel

Use SUBSTITUTE's fourth argument, the instance number: =SUBSTITUTE(A2,"-"," ",3) replaces only the third dash.

**Excel:** `=SUBSTITUTE(A2,"-"," ",$E$1)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Exported timestamp | Fixed |  | Occurrence | 3 |
| 2026-03-14-09:30 |  |  |  |  |
| 2026-03-15-14:05 |  |  |  |  |
| 2026-04-01-08:00 |  |  |  |  |
| 2026-04-02-17:45 |  |  |  |  |
| 2026-04-09-23:59 |  |  |  |  |

Formula in B2, filled to B6: `=SUBSTITUTE(A2,"-"," ",$E$1)`

Result: 2026-03-14 09:30; 2026-03-15 14:05; 2026-04-01 08:00; 2026-04-02 17:45; 2026-04-09 23:59

## How it works

Without its fourth argument, SUBSTITUTE replaces every match. With an instance number, it replaces only that one, counting from the left. Here the export joined the date and time with a dash, and replacing the third dash with a space gives a timestamp that converts to a real date and time.

## Other ways

- `=REPLACE(A2,FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),$E$1)),1," ")`: The long way: find the nth dash's position, then REPLACE that one character. Useful when you need the position anyway.

## Common mistakes

- The instance counts from the left only; there is no negative instance. For the last occurrence, use the count as the instance: `LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))`.
- If the text has fewer matches than the instance number, SUBSTITUTE returns it unchanged, with no error.

## Questions

**How do I turn the result into a real date and time?**

Wrap it in VALUE: `=VALUE(SUBSTITUTE(A2,"-"," ",3))`, then format the cell as a date and time.

**How do I replace the last occurrence?**

Use `=SUBSTITUTE(A2,"-"," ",LEN(A2)-LEN(SUBSTITUTE(A2,"-","")))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/replace-nth-occurrence-of-a-character