Mid formula, but stop if there is a space?

D4WNO

Board Regular
Joined
Mar 6, 2012
Messages
67
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:
[TABLE="width: 122"]
<tbody>[TR]
[TD]Tue, Jul 31 09:38:21 BST 2018

and

Tue, Mar 6 14:26:37 GMT 2018[/TD]
[/TR]
</tbody>[/TABLE]

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)
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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:
Code:
=MID(M2,6,(FIND(":",M2)-4)-(FIND(",",M2)))
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Heres one way:

=0+TRIM(MID(SUBSTITUTE(REPLACE(A1,FIND("@",SUBSTITUTE(A1," ","@",3)),99,""),",",LEFT(A1,3)),4,99))
 
Upvote 0
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!
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,182
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top