• Quick note - the problem with Youtube videos not embedding on the forum appears to have been fixed, thanks to ZiprHead. If you do still see problems let me know.

need spreadsheet help

jimtron

Illuminator
jimtron
Joined
Mar 9, 2005
Messages
3,105
Location
Los Angeles, California
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
Code: 
2009-09-07
but also a bunch like this
Code: 
2009-09-07-A01
2009-09-07-A02
I 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!
 
Last edited:
nimzov said:
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
Click to expand...

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?
 
Last edited:
jimtron said:
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?
Click to expand...

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
 
Last edited:
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.")
 
nimzov said:
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.
Click to expand...


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.
 
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:
Code: 
=IF(ISNUMBER([B][I]A1[/I][/B]),[B][I]A1[/I][/B],DATE(VALUE(MID([B][I]A1[/I][/B],1,4)),VALUE(MID([I][B]A1[/B][/I],6,2)),VALUE(MID([I][B]A1[/B][/I],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.
 

Attachments

FWIW, my approach would be:

Code: 
=DATEVALUE(LEFT(A1,10))

and, as above, copy the formula down.
 
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.
 
You must log in or register to reply here.

Back
Top Bottom