converting text to date format

[Amitfre1]

I need to convert the following text format into a date format so that the 1st sequence of numbers will be deleted, the 2nd sequence will be treated as the month, and the 3rd will be the year.

See the example below. How should I use the text-to-columns function properly to reach that?

30858-05-22	May-22
37020-11-22	November-22
14858-06-22	June-22
30333-01-22	January-22
65113-04-21	April-21
6165-07-16	July-16

[#2]

@Amitfre1 

=TEXT(MID(A1,SEARCH("-",A1)+1,LEN(A1)-SEARCH("-",A1)+1),"MMMM-YY")

Does this return the intended result?

[#3]

@OliverScheurich yes for the month, not for the year..

[#4]

@Amitfre1 

=TEXT(MID(A1,SEARCH("-",A1)+1,LEN(A1)-SEARCH("-",A1)+1),"MMMM-YY")

Does it work with YY instead of JJ?

[#5]

@OliverScheurich It does, thank you so much! However, I cannot change the date format at all. is there a way to overcome this? Thanks

[#6]

@Amitfre1 

You are welcome. In cell B1 you can apply this formula as well:

=TEXT(RIGHT(A1,LEN(A1)-SEARCH("-",A1)),"MMMM-YY")

In cell C1 is this formula which returns the date:

=DATEVALUE(B1)

The number format of the cells in column C is date ("Datum").

Or you can do this in one step:

=DATEVALUE(TEXT(RIGHT(A1,LEN(A1)-SEARCH("-",A1)),"MMMM-YY"))

The result is returned in column D.

[#7]

@OliverScheurich Thank you!

2 problems that I still have:

1. The formula does not apply for years 2000's and 90's and so on.. for example- 

   12026-09-10

   866-03-08

2. I also have these types of strings which should return the year only- 

   5904-06

   58224-07

   2209-04

   3529-08

[#8 · Accepted solution]

@Amitfre1 

=DATE(NUMBERVALUE(RIGHT(A1,2))+IF(NUMBERVALUE(RIGHT(A1,2))>25,1900,2000),NUMBERVALUE(MID(A1,7,2)),1)

An alternative could be this formula. Since it's unknown if e.g. 45145-02-11 is february 2011 or february 1911 i've added an IF statement which returns the 90s or 80s.... if the last two digits are greater 25. The 2000's are returned if the last two digits are 25 or less.

=DATE(NUMBERVALUE(RIGHT(A1,2))+IF(NUMBERVALUE(RIGHT(A1,2))>25,1900,2000),1,1)

For the second question you can try this formula.

[#9]

@Amitfre1 

This is 365 only.  I might also have missed some irregularities in the 'text' data you wish to process.

= DATEVALUE(TEXTAFTER(text, "-"))

[#10]

@OliverScheurich Hey, both work perfectly. Thank you so much!