# String to date?

#### eduzs

##### Well-known Member
IS there a formula to get the date from a string like that: "wedsneday, 14 of june of 2016"??
Thanks

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### 63falcondude

##### Well-known Member
Sure, but it won't be very pretty. Assuming that you have multiple text dates that you want to convert to excel recognized dates, are they all in that same format?

#### ArnoldMS

##### Banned
Hello try this formula:

#### Rick Rothstein

##### MrExcel MVP
IS there a formula to get the date from a string like that: "wedsneday, 14 of june of 2016"??
Give this formula a try...

=0+MID(SUBSTITUTE(LOWER(A1),"of ",""),FIND(" ",A1)+1,99)

This formula returns the date's serial number so you will need to apply the Date Cell Format of your choice to the cell you put it in.

#### ArnoldMS

##### Banned
Hello try this formula:

Sorry wrong one, use this one instead:

#### 63falcondude

##### Well-known Member
Give this formula a try...

=0+MID(SUBSTITUTE(LOWER(A1),"of ",""),FIND(" ",A1)+1,99)

This formula returns the date's serial number so you will need to apply the Date Cell Format of your choice to the cell you put it in.

Impressive...

#### godsaaint

##### Active Member
Give this formula a try...

=0+MID(SUBSTITUTE(LOWER(A1),"of ",""),FIND(" ",A1)+1,99)

This formula returns the date's serial number so you will need to apply the Date Cell Format of your choice to the cell you put it in.

I have tried your formula and another of mine, it works fine up until it has to sum the 0, basically, its doing "0+ 14 june 2016" which is returning #VALUE, any idea how to approach this?

#### Rick Rothstein

##### MrExcel MVP
I have tried your formula and another of mine, it works fine up until it has to sum the 0, basically, its doing "0+ 14 june 2016" which is returning #VALUE, any idea how to approach this?
Are you saying the formula I posted is not working for you? Did you use it exactly like I posted it (except for changing the cell reference is your date string is not in cell A1)? Just so you know, the formula I posted works fine for me.

#### godsaaint

##### Active Member
Correct. I'm trying exactly the one you posted, same reference, "wedsneday, 14 of june of 2016"(no quotes) in A1, and it's returning #Value!

Last edited:

#### Rick Rothstein

##### MrExcel MVP
Correct. I'm trying exactly the one you posted, same reference, "wedsneday, 14 of june of 2016"(no quotes) in A1, and it's returning #Value!
Maybe your spaces are not actually "spaces" (ASCII 32)... if you got your data from the web, there is a good chance they are non-breaking spaces (ASCII 160). See if this version of my formula works for you...

=0+MID(SUBSTITUTE(LOWER(SUBSTITUTE(A1,CHAR(160)," ")),"of ",""),FIND(" ",SUBSTITUTE(A1,CHAR(160)," "))+1,99)

Replies
23
Views
672
Replies
14
Views
258
Replies
1
Views
154
Replies
4
Views
450
Replies
9
Views
131

1,191,168
Messages
5,985,054
Members
439,936
Latest member
BSR

### 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.

### Which adblocker are you using?

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

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