Convert date to month name

[ANE0709]

i have a date range in J that shows "1/9/2022 - 1/15/2022" which i need to convert to show the name of the month for the first date only. I was using =text(left(J2,10),"mmmm") before my file was corrupted. now im starting over and putting all formulas in from scratch. this one was working but isnt anymore. Any ideas why not and how to fix it?

 

[Jon Peltier]

Why? The TEXT function is not bothered by the trailing space. Given that, and on the off chance that occasionally there might not be a space between the date and dash, this might be slightly better...

=TEXT(LEFT(J2,FIND("-",J2))-1,"mmmm")

OCD? I don't know.

Besides, if there might not be a space, the original specs are violated and all previous responses are wrong, with or without the extra space.

 

[Rick Rothstein]

Besides, if there might not be a space, the original specs are violated and all previous responses are wrong, with or without the extra space.

Okay, you are probably right about that, but here is another way to do what the OP wants which does not care about the text past the first two characters...

=TEXT(28*SUBSTITUTE(LEFT(A1,2),"/",""),"mmmm")

Yeah, I know... no one is going to use this formula... I just posted it for its interest value.

 

[jtakw]

Besides, if there might not be a space, the original specs are violated and all previous responses are wrong, with or without the extra space.

Hi,

If there is no space, @Rick Rothstein formula in Post # 10 would still work.

Why? The TEXT function is not bothered by the trailing space. Given that, and on the off chance that occasionally there might not be a space between the date and dash, this might be slightly better...

=TEXT(LEFT(J2,FIND("-",J2))-1,"mmmm")

Although I think Rick had a typo and actually mean this:

=TEXT(LEFT(J2,FIND("-",J2)-1),"mmmm")

Now, what if there was No Space, and No Hyphen (or even no slashes "/") at all?
I think we can only work with what OP supplied as sample data, yes?

Book3.xlsx
	J	K	L	M	N	O
1		Post # 3	Post # 9	Post # 10	Post # 10 corrected	Post # 12
2	1/9/2022 - 1/15/2022	January	January	#VALUE!	January	January
3	1/9/2022-1/15/2022	#VALUE!	#VALUE!	#VALUE!	January	January
4	1/9/20221/15/2023	#VALUE!	#VALUE!	#VALUE!	#VALUE!	January
Sheet990

Cell Formulas
Range	Formula
K2:K4	K2	=TEXT(LEFT(J2,FIND(" ",J2)),"mmmm")
L2:L4	L2	=TEXT(LEFT(J2,FIND(" ",J2))-1,"mmmm")
M2:M4	M2	=TEXT(LEFT(J2,FIND("-",J2))-1,"mmmm")
N2:N4	N2	=TEXT(LEFT(J2,FIND("-",J2)-1),"mmmm")
O2:O4	O2	=TEXT(28*SUBSTITUTE(LEFT(J2,2),"/",""),"mmmm")

 

[Rick Rothstein]

Although I think Rick had a typo and actually mean this:

=TEXT(LEFT(J2,FIND("-",J2)-1),"mmmm")

Now, what if there was No Space, and No Hyphen (or even no slashes "/") at all?
I think we can only work with what OP supplied as sample data, yes?

I sure did have a typo!!! Thanks for catching it.

 

[jtakw]

I sure did have a typo!!! Thanks for catching it.

You're welcome.
@Jon Peltier has a similar typo in Post #9, as it is, it's subtracting 1 day from the LEFT function result.