Read a timestamp split by a separator

Turn the text 4-Nov-2026 | 14:30:15 into a real date and time.

What you'll make

In Excel: 4-Nov-2026 | 14:30:15 becomes November 4, 2026 2:30:15 PM.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTBEFORE(A8,B8)Takes the date text before the separator."4-Nov-2026"
2DATEVALUE( … )Turns that text into a real date.November 4, 2026
3TEXTAFTER(A8,B8)Takes the clock text after the separator."14:30:15"
4TIMEVALUE( … )Turns the clock text into a time and adds it.… 2:30:15 PM

Read it left to right. Each pair sits one inside the other, so Excel works from the inside out.

=DATEVALUE(TEXTBEFORE(A8,B8))+TIMEVALUE(TEXTAFTER(A8,B8))

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own timestamps. Paste text like 4-Nov-2026 | 14:30:15 into column A of the Template tab and its separator into column B. The dates and times fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Type the separator exactly as it appears, spaces included. Each row needs it once, with a four-digit year.

Microsoft's help: TEXTBEFORE function