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.
Substitute, Trim, Len etc. (2003 SP3)
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Substitute, Trim, Len etc. (2003 SP3)
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Substitute, Trim, Len etc. (2003 SP3)
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 ","")
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 ","")
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Substitute, Trim, Len etc. (2003 SP3)
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.
=MID(A1,21,FIND(" ",RIGHT(A1,LEN(A1)-20))-1)&" minutes ago"
Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- SilverLounger
- Posts: 1952
- Joined: 26 Jan 2010, 12:46
- Location: Nr. Heathrow Airport
Re: Substitute, Trim, Len etc. (2003 SP3)
Hi Hans and Andy
Thank you very much for your advice, most appreciated
Thank you very much for your advice, most appreciated
Steve
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin
http://www.freightpro-uk.com" onclick="window.open(this.href);return false;
“Tell me and I forget, teach me and I may remember, involve me and I learn.”
― Benjamin Franklin