Substitute, Trim, Len etc. (2003 SP3)

[steveh]

Good afternoon

I have been tring to teach myself the use of some of the forumla functions but I have got stuck on one part in particular. In a cell the imported words say - The report was made 12 minutes ago 14:00 UTC - I would like the target cell to only say 12 minutes ago, I can remove the first part - The report was made - by using this formula =SUBSTITUTE(B1,"The report was made","") but I can't figure out how to remove the trailing part, please bear in mind that the 14:00 will be a moveable feast because the data is imported from the web. I have tried to combine TRIM, LEN and LEFT into the formula to catch the last part but it always results in #Value.

[HansV]

Let's say your text is in A1.
The following array formula (confirm with Ctrl+Shift+Enter) will extract the "12 minutes ago" part:

=SUBSTITUTE(LEFT(A1,LARGE(ROW(INDIRECT("1:"&LEN(A1)))*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" "),2)-1),"The report was made ","")

[agibsonsw]

Thought I would make my small contribution: If the text always begins "The report was made " (20 characters) you could use the non-array formula:
=MID(A1,21,FIND(" ",RIGHT(A1,LEN(A1)-20))-1)&" minutes ago"
Andy.

[steveh]

Hi Hans and Andy

Thank you very much for your advice, most appreciated