0

I have a string below which updated every 10 or so minutes.

Last Updated as of 8:22 am Auguest 30th blah blah blah

I am interested only in 8:22 am. I know I can use =MID() & =find() to grab it but does anybody have easier way to do it ?

Mowgli
  • 3,422
  • 21
  • 64
  • 88
  • 1
    Not really, no. AFAIK, `MID()` + `FIND()` is going to be your best bet. You *could* use VBA by including `'Microsoft VBScript Regular Expressions` and the use regular expressions (http://superuser.com/questions/181005/can-you-do-regular-expressions-in-excel-without-vbscript), but yuck. – LittleBobbyTables - Au Revoir Aug 30 '13 at 13:01
  • Is "blah blah blah" constant, or does that part change? – Doug Glancy Aug 30 '13 at 13:32
  • @DougGlancy that part does also changes but only in length. from 1 to 999,999. – Mowgli Aug 30 '13 at 14:45
  • @pnuts lol, You are right both give us same solution. I guess all I can tell you that I was looking for different solution then `=mid()` which I already knew. I learned something new with Doug's solution. – Mowgli Aug 30 '13 at 18:05

3 Answers3

3

Since Last Updated as of is always

you can use = Mid(A1,20,8) for A1 Cell

matzone
  • 5,703
  • 3
  • 17
  • 20
  • It is dynamic so lets say for example next month is May, and I already use that. – Mowgli Aug 30 '13 at 13:12
  • 1
    Month name won't a problem .. as you said `I am interested only in 8:22 am` .. or I've missed something ? – matzone Aug 30 '13 at 15:09
1

Since the width of the time will only vary by one character, I think this works for you:

=TRIM(LEFT(SUBSTITUTE(A1,"Last Updated as of ",""),8))
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • This is clever Thanks. – Mowgli Aug 30 '13 at 14:49
  • 1
    I think it's easier than `Mid` and `Find`, but matzone's is simpler still, although I would add `TRIM` to it as well. As far as "hidden features" I make no claims in that regard. I'm just a humble answerer of questions. – Doug Glancy Aug 30 '13 at 19:45
0

MID and FIND would be the fastest/easiest way to pull that information out.

Darryl Gerrow
  • 167
  • 3