Extracting month from text

[XcelLearner]

I have a (sample) data set with dates in text format in no particular order. They look like this:

4/10/2020
11/10/2020
10/10/2020
11/17/2020

They look like in date format, but they are texts. I would like to extract the number before the stroke "/" (that is month: April would be 4, not 04, and November should be 11.), so that the desired results should be (in number format):
4
11
10
11.

What is the possible formula(e)?
Thanks a lot.

 

[jimrward]

Take a look at the excel functions LEFT and also FIND and combine the two

 

[XcelLearner]

Take a look at the excel functions LEFT and also FIND and combine the two

Actually, I've tried to combined LEFT and FIND, and even with SEARCH, but they kept giving me #Value errors. I might have used them incorrectly, these are functions that I've rarely used. Thanks.

 

[Oaktree]

=MONTH(A2+0)

 

[XcelLearner]

=MONTH(A2+0)

It works perfectly. Thank you very much. Do you mind if I ask, the "+0" is to make the result in number format?

 

[Joe4]

Do you mind if I ask, the "+0" is to make the result in number format?

Yes, I believe that is his intention.
If it is already a valid date entry, it won't hurt it anything. So it just covers both bases.

Out of curiosity, I did some testing on my side, and even with dates entered as text, this seemed to work without the "+0", i.e.
=MONTH(A2)

Not what I expected, but pleasantly surprised.

 

[Dave Patton]

N.B. The alternative below is relevant with Regional Settings International - dd-mm-yy.

T202012a.xlsm
	A	B
1	Text Data
2	4/10/2020	4
3	11/10/20	11
4
1a

Cell Formulas
Range	Formula
B2:B3	B2	=LEFT(A2,FIND("/",A2)-1)

 

[Oaktree]

Yep. That's exactly right. +0 will convert a date stored as text to an Excel date. As Joe4 pointed out, it's not needed here, but I like to include it anywhere I'm referencing a date stored as text. Once you've been burned by a (date stored as text) < (date for comparison) comparative (where *all* dates stored as text would return "False"), you know why

 

[Joe4]

As Joe4 pointed out, it's not needed here, but I like to include it anywhere I'm referencing a date stored as text. Once you've been burned by a (date stored as text) < (date for comparison) comparative (where *all* dates stored as text would return "False"), you know why

Yep, better safe than sorry!

I was actually a bit surprised that the MONTH function worked on a text entry. I would not have expected that.