need spreadsheet help

[jimtron]

I've got a spreadsheet on Google docs, and my experience with spreadsheets is very limited. There's a date column, with most dates like this 2009-09-07but also a bunch like this 2009-09-07-A01
2009-09-07-A02I want to lose the suffixes, and just keep the dates (yyyy-mm-dd).

There are a lot of entries, so I'd rather not manually delete the extra characters. Is there a formula I can do this with, maybe with wildcards or something? Or search and replace (except the suffixes are not all the same)?

I can export this and open it in Open Office, if that would be better.

Thanks!

[nimzov]

Use the LEFT function to extract the first 10 characters.

With the data in the A column, put in your B1 cell the formula =LEFT(A1, 10)
Copy this formula for all cells.

http://www.brighthub.com/computing/windows-platform/articles/26942.aspx

nimzo

[jimtron]

Use the LEFT function to extract the first 10 characters.

With the data in the A column, put in your B1 cell the formula =LEFT(A1, 10)
Copy this formula for all cells.

http://www.brighthub.com/computing/windows-platform/articles/26942.aspx

nimzo

Thanks! I just tried that with one row and it did the trick. You said copy the formula, but don't I have to specify the row? I have about a thousand rows.

eta: My date column is "F." I created a new, empty column, "G." I just pasted this command "=LEFT(F11, 10)" into the 11th row, and I got the date the way I wanted in the G column. How do I apply this to all rows without manually inserting the row number, which would likely take longer than just deleting the extra characters?

[nimzov]

My date column is "F." I created a new, empty column, "G." I just pasted this command "=LEFT(F11, 10)" into the 11th row, and I got the date the way I wanted in the G column. How do I apply this to all rows without manually inserting the row number, which would likely take longer than just deleting the extra characters?

You copy this formula to the right of each cell with data in your F column.

For example if your data is in cells from F1 to F1000, then you simply copy the formula in the cells from G1 to G1000.

Method:

1- Enter the formula in cell G1
2- Select the cell G1 then Crtl-C to copy
3- Then select cells G2 to G1000 and Ctrl-V to paste the formula in each of these cells.

nimzo

[jimtron]

Thanks, I just figured it out after reading more from the link you supplied (I used the fill handle, which I didn't know about: "Once we have the formulas entered into each of the initial cells, we can select those three, grab the fill handle, and copy the formulas all the way down.")

[Corsair 115]

Use the LEFT function to extract the first 10 characters.

With the data in the A column, put in your B1 cell the formula =LEFT(A1, 10)
Copy this formula for all cells.


That will indeed work. But there's one very important caveat:

The results in the cells with the formula are not considered dates by Excel. This can be verified by trying to change the date format for the result cells—there is no change in how the cells are displayed. I don't know what Excel considers those cells to contain, but it most certainly isn't a date. Even if you copy the results from the formula cells and paste them in elsewhere as values, Excel will still not treat the pasted result as a date value.

The only way I've found to get Excel to treat the results as dates is by copying the cells into Wordpad or Notepad and then pasting them back into Excel. Then the cell results will be accepted as dates.

I mention this because it's something I've run into numerous times. It's very irritating.

[Bob Blaylock]

If you paste those into Excel, then the entries that do not have the suffix will be immediately parsed as dates, and converted accordingly. Those that have the suffix will remain as strings.

Here's a formula that will work in all cases on entries originating in the format that you've given:=IF(ISNUMBER(A1),A1,DATE(VALUE(MID(A1,1,4)), VALUE(MID(A1,6,2)),VALUE(MID(A1,9,2))))

Just change every occurrence of “A1” to a valid reference to the cell to be converted.

Be advised, also, that this formula returns a numeric value that Excel doesn't know to treat as a date, so you'll need to explicitly format the cells containing this formula to be displayed as dates.

The attached file DATES.ZIP contains an example Excel workbook applying this formula to the example data that you provided.

[Worm]

FWIW, my approach would be:

=DATEVALUE(LEFT(A1,10))

and, as above, copy the formula down.

[The Man]

To get rid of the formulas in the cells once the results are the way you want them, just copy the column and under the Edit drop down select Paste Special and check only the Values check box. This will replace the formulas with the current result values.

[sphenisc]

I did


=LEFT(A1,10)*1


and formatted as Date