A Text to Columns function for Google's Spreadsheets
This covers 2 topics:- Change non-standard text format to a date -- using a formula
- Change an existing values in a Google Spreadsheet -- using a custom script.
Using a Google Sheet formula to convert YYYYMMDD to a standard date
I was downloading data from a Google Analytics Customized Report into a CSV file which I wanted to use in a Google Docs spreadsheet.Example data:
| Date | Sessions | Pageviews | Bounce Rate | Pages / Session | New Users |
| 20150101 | 2,361 | 12,728 | 3.18% | 5.39 | 1,903 |
| 20150102 | 2,137 | 12,804 | 3.09% | 5.99 | 1,672 |
| 20150103 | 1,833 | 12,879 | 3.06% | 7.03 | 1,473 |
| 20150104 | 1,816 | 12,866 | 2.97% | 7.08 | 1,483 |
Delimiting the fixed width columns
As you can see, the first column has a genuine date in it, but it looks funky because it is not delimited. There is not slash or dash between the values of year, month and day.I wanted to change YYYYMMDD into MM/DD/YYYY; and then change the resulting values to dates that the spreadsheet can work with. MS Excel can do this using a command called "Text to Columns"
I used this information from this page to create a formula that takes the fixed width value from a specified cell (in this case, A6), rearranges the order of the elements, and adds delimiters.
=DATEVALUE(Mid(A6,5,2) & "/" & Right(A6,2) & "/" & Left(A6,4))
Ex. Using this, "20150104" the function DATEVALUE converts the 8-character string or number into the U.S. date value "01/04/2014" (aka January 4, 2014).
But the problem is, the formula can't be entered into the original cell, right? The formula would replace the cell's value.
So, to see the correct value, you would have to place the formula into a different, blank cell. Perhaps, you might even create a new column to hold the value. Or, if you're like me, you want the new value to be in the same cell as the original value.
Using a Script to format all selected cells
Scripts can be used to add functionality to a document that doesn't already exist.There's two parts to the process --
- Add an item to the menu bar,
- Add a function that will execute when the menu item is selected.
As of January 2015, these are the steps to add the script:
- Go to Tools > Script Editor...
The menu setup will contain a specific functionName to launch.
A second function is then created to invoke the specified functionName.
Here, we'll call the function name: convertDateYYYYMMDD.
The function first retrieves the values in the currently selected cells and saves them as an array called values.
A second blank array is created called newValues.
The script then loops through the current values and, if a value is not blank, it rearranges substrings taken from the date and inserts slashes between them. So, YYYYMMDD becomes MM/DD/YYYY.
Then it replaces the currently selected cell values with the new array.
- To add the menu and functionality, paste this code into your Script Editor.
// ADDS MENU ITEM
function onOpen() {
SpreadsheetApp.getActive().addMenu(
'Convert', [{name:'Convert Date: YYYYMMDDD', functionName:'convertDateYYYYMMDD'}]);
}
// APPLY NEW DATE FORMAT TO SELECTED CELLS
function convertDateYYYYMMDD() {
var values = SpreadsheetApp.getActiveRange().getValues();
var newValues = new Array();
for (i = 0; i < values.length; i++) {
if (values[i][0] != '') {
newValues.push(['=DATEVALUE(Mid(' + values[i][0] + ',5,2) & "/" & Right(' + values[i][0] + ',2) & "/" & Left(' + values[i][0] + ',4))']);
} else {
newValues.push(['']);
}
}
SpreadsheetApp.getActiveRange().setValues(newValues);
}
- When done, hit the Save button, and if this is new, name the project something meaningful to you.
- You should see a new menu item: [Convert]. If not, you may need to save and reload your sheet.
- If you see the menu item, first select the text you want to convert, then go to [Convert > Convert Date: YYYYMMDD].
- The text may not look like what you were expecting. So, the final thing that you may need to do is to convert the selected cells to a date format. Go to:
Format > Number > Date
