Mid formula, but stop if there is a space?

[D4WNO]

Hi everyone,

I'm sure this is simple but I can't find the solution online. Is there a way I can use a MID formula like this that follows this, unless there is a space, in which case it stops earlier?

I have text, for example:

Tue, Jul 31 09:38:21 BST 2018 and Tue, Mar 6 14:26:37 GMT 2018

<tbody>
</tbody>

Using the below formula, I correctly get "Jul 31" for my first example, but for my 2nd example, it comes back as "Mar 6 1"

=MID(M2,6,7)

 

[philwojo]

It's Monday, it's early and my brain isn't fully going yet, but this should work, though I am sure there is a much better way.

Try this:

=MID(M2,6,(FIND(":",M2)-4)-(FIND(",",M2)))

 

[steve the fish]

Are you after text or a real date? Heres a date:

=DATEVALUE(LEFT(REPLACE(A1,1,5,MID(A1,10,2)),5)&RIGHT(A1,4))

That may or may not work depending on your date formats. This produces 31Jul2018 which excel can interpret as a date in my locale.

 

[D4WNO]

This is absolutely perfect, thank you soooo much!!

 

[D4WNO]

If I wish to do the same for a date field in the format below, how would this calculation change? I've been trying to figure it out for ages and not getting the right results back? I'd like it to come back as 1 Oct 2018 as in the example kindly given by "steve the fish" above. My brain hurts

Oct 1, 2018 07:27 AM

 

[steve the fish]

Heres one way:

=0+TRIM(MID(SUBSTITUTE(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),99,""),",",LEFT(A1,3)),4,99))

 

[D4WNO]

Errrrr, so I never would have come up with that, right over my head....but it works! Thanks again @stevethefish

I'm off to try figure out how this works!

 

[steve the fish]

Heres one a fair bit shorter that makes assumption that the time is always there and is always 8 digits in length:

=0+SUBSTITUTE(MID(A1,5,LEN(A1)-12),",",LEFT(A1,3))