Find the Last Text Occurrence and Extract Date Time in Excel
Question details
Find the final occurrence of a status entry in a text string and extract the date and time associated with it.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A cell contains multiple status records, and the user needs the date and time after the final status or final @ symbol.
- Observed behavior
- The FIND function returns only the first occurrence of text, rather than the last occurrence needed for the final status record.
Confirm that the source text is in cell A1 and that each timestamp consistently uses the @, T, and Z delimiters. Choose the Microsoft 365 formula only if your Excel version supports TEXTAFTER, TEXTSPLIT, LET, and CHOOSECOLS.
Use TEXTAFTER to extract the final status date and time in Microsoft 365
Use this option when you have Microsoft 365 and want to isolate the last "Status " entry before converting its timestamp into an Excel date and time value.
TEXTAFTER with an instance number of -1 returns the text after the last occurrence of "Status ". TEXTSPLIT then separates the timestamp at @, T, and Z so Excel can calculate a date-time value.
Click an empty cell where you want the extracted date and time to appear, such as B1.
Type or paste the following formula, replacing A1 if your text is in another cell: =LET(finalStatus,TEXTAFTER(A1,"Status ",-1),dateAndTime,TEXTSPLIT(finalStatus,{"@","T","Z"}),SUM(VALUE(CHOOSECOLS(dateAndTime,{2,3})),-5/12))
Select the formula cell, open Home > Number Format, and choose Short Date, Long Date, or More Number Formats > Custom to apply a format such as m/d/yyyy h:mm AM/PM.
Change the final offset value in the formula to match your required conversion. For example, use a negative fraction of a day for a time zone behind UTC.
Extract the text after the final @ symbol with a legacy Excel formula
Use this formula in older Excel versions that do not include TEXTAFTER or TEXTSPLIT.
Extract the Last Timestamp from Text with WPS Spreadsheets
WPS Spreadsheets can use a compatible legacy formula to retrieve text after the final @ symbol, convert the timestamp to a date-time value, and format the result for reporting or analysis.
- 1. Open the worksheet in WPS Spreadsheets: Open your Excel workbook in WPS Office and select the worksheet containing the text string.
- 2. Choose a result cell: Click an empty cell next to the source text, such as B1 when the source string is in A1.
- 3. Enter the compatible extraction formula: Enter =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"@",REPT(" ",255)),255)),"T"," "),"Z",""),"|","")-5/24 and press Enter.
- 4. Set the date and time format: Select the result cell and use Home > Number Format to choose a date-time format that shows both the date and hour.
Frequently Asked Questions
Why does FIND return the first match instead of the last match in Excel?
FIND searches from the beginning of the text and returns the first matching position. To work with the final match, use TEXTAFTER with an instance number of -1 in Microsoft 365, or use a SUBSTITUTE-based legacy formula.
How do I return the text after the last @ symbol in Excel?
For older Excel versions, enter =TRIM(RIGHT(SUBSTITUTE(A1,"@",REPT(" ",255)),255)) in an empty cell. This returns the text after the final @ symbol in A1.
Why is the extracted timestamp displayed as a number?
Excel stores dates and times as serial numbers. Select the result cell, go to Home > Number Format, and choose a date-time format such as m/d/yyyy h:mm AM/PM.
How can I change the timestamp time-zone adjustment?
Edit the final offset in the formula. For example, -5/24 subtracts five hours, while +2/24 adds two hours. One full day equals 1 in Excel date-time calculations.
