Forum Discussion
Hsksmails
Mar 04, 2024Copper Contributor
Can i use FIND to search last occurence
So I have a string Complete Status 123@2023-03-01T16:25:51.094Z Status 456@2023-03-01T16:22:46.227Z| Status XYZ@2023-03-01T19:43:56.184Z Status 456@2023-02-28T18:24:23.7292| Status XYZ@2023-02-...
HansVogelaar
Mar 04, 2024MVP
With such a string in A1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, "@", REPT(" ", 255)), 255)), "T", " "), "Z", ""), "|", "")-5/24
Format as date + time.
- HsksmailsMar 05, 2024Copper Contributor
Thanks for your response
Can you please elaborate a bit if u don't mind
If A1 is the complete string ,where is the sub string that I am looking for?
Asking since I don't see the sub string that I am searching for anywhere in the formula
- HansVogelaarMar 05, 2024MVP
The formula extracts the part of the string after the last occurrence of @.
It then removes "Z" and "|" from that part and replaces "T" with a space.
That is the date and time you're looking for. Finally it subtracts 5/24 to convert from GMT to EST.