How to get date from string e.g "05th February 2015" in google sheets

Question

Hi I need a formula that converts a string in the format:

"05th February 2015"

To data value. Unfortunately this is the raw data format that has to be used.

The only way currently I can think might be possible to do this is to chop the string for the day and year and then use an if statement to change the month name into a number, then use the DATEVALUE function.

Does anyone know of a solution?

I think it will help http://stackoverflow.com/questions/8206245/mmmm-yy-date-in-google-spreadsheet — planet260, Mar 12 '15 at 12:50
thanks but I cant see how that would help as this is to convert a number into text. — adamsportstech, Mar 12 '15 at 12:53

score 0 · Answer 1 · answered Feb 03 '18 at 19:04

Please try:

=1*(left(A1,2)&right(A1,len(A1)-4))

then format to suit.

The issue here with persuading Sheets to recognise a date in text format is mainly to remove the ordinal indicators. This can be achieved by picking off the first two characters and concatenating them to all the remaining characters other than the ordinal indicators. (Sheets offers various other ways to do so with corresponding results.)

Once the appearance is recognisable by Sheets it is just a matter of coercing Sheets to convert from text to number format, which can be achieved by 'multiplying' by 1 (again, Sheets does have other options for this).

For the above to work the text for month name must suit the spreadsheet setting locale.

How to get date from string e.g "05th February 2015" in google sheets

1 Answers1