Splitting date from number string

[brizz]

Hello, I have a spreadsheet with 1000+ dates that are listed in this format...

20150415

And I need it in this format: 04/15/2015, or with each year/month/date split into individual cells?

Is there some way to separate the year, month, and day from this numerical string? I've tried formulas to remove the first x number of characters, but it seems to just copy them into another cell and doesn't effect the original column.

 

[FDibbins]

Try this...
[Table="width:, class:grid"][tr][td] [/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

20150415​

[/td][td]

4/15/2015​

[/td][/tr]
[/table]
H1=DATE(LEFT(G1,4),MID(G1,5,2),RIGHT(G1,2))

You would put this in a helper column next to your dates, then copy down

 

[Rick Rothstein]

Here is another formula that you can use...

=0+TEXT(A1,"0000-00-00")

After putting the formula in a cell, format that cell with the Date format that you want to display it as.

 

[brizz]

Try this...
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

20150415​

[/TD]
[TD]

4/15/2015​

[/TD]
[/TR]
</tbody>[/TABLE]

H1=DATE(LEFT(G1,4),MID(G1,5,2),RIGHT(G1,2))

You would put this in a helper column next to your dates, then copy down

Ahh that worked wonderfully! Thanks!

 

[Rick Rothstein]

Here is another formula that you can use...

=0+TEXT(A1,"0000-00-00")

After putting the formula in a cell, format that cell with the Date format that you want to display it as.

If, on the other hand, you wanted to physically change those values to real dates directly with the cells they are located in, you can do it this way... select the column with your dates, call up the Text-To-Columns dialog box (Data tab, Data Tools pane), click the Next button twice as soon as the dialog box appears, select the Date option button (upper left quarter of the dialog box) and select YMD from the dropdown box next to it and then click the Finish button.

 

[FDibbins]

Rick, I tried DATEVALUE() which obviously did not work, did not even think to try T2C - nice catch