<body><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener('load', function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <div id="navbar-iframe-container"></div> <script type="text/javascript" src="https://apis.google.com/js/plusone.js"></script> <script type="text/javascript"> gapi.load("gapi.iframes:gapi.iframes.style.bubble", function() { if (gapi.iframes && gapi.iframes.getContext) { gapi.iframes.getContext().openChild({ url: 'https://www.blogger.com/navbar.g?targetBlogID\x3d8211560\x26blogName\x3dTech+Tips,+Tricks+%26+Trivia\x26publishMode\x3dPUBLISH_MODE_BLOGSPOT\x26navbarType\x3dBLUE\x26layoutType\x3dCLASSIC\x26searchRoot\x3dhttp://mvark.blogspot.com/search\x26blogLocale\x3den\x26v\x3d2\x26homepageUrl\x3dhttp://mvark.blogspot.com/\x26vt\x3d-5147029996388199615', where: document.getElementById("navbar-iframe-container"), id: "navbar-iframe" }); } }); </script>

Tech Tips, Tricks & Trivia

by 'Anil' Radhakrishna
An architect's notes, experiments, discoveries and annotated bookmarks.

Search from over a hundred HOW TO articles, Tips and Tricks


Data Scrubbing with Google Sheets formulas

While scrubbing data in Google Sheets for a hobby project, I discovered the following formulas that made the job easy -

1) If you have a raw string like "Blah|2|4|5|www.google.com", the below formula can split the values separated by the (pipe) delimiter & place them in a new column in a row

In cell B2 paste the following:
=ARRAYFORMULA(IFERROR(SPLIT(A1:A;"|")))
Then paste your data starting with cell A2. As soon as you enter the data it will automatically split the combined values to a value-per-column using the '|' as a delimiter.

2) To remove duplicate values from rows in a column and then show them in sorted order, I used this forumula: =SORT(UNIQUE(A1:A151))

3) REGEXEXTRACT xtracts matching substrings according to a regular expression.

This formula will pick a group of numbers from the value at cell A2
=REGEXEXTRACT(A2, "[\d]+")

4) There is no in-built formula to parse JSON output but it is possible to create custom formulas with Google App Script

Example:
=ImportJSON("https://api.stackexchange.com/2.2/tags/" & J11 & 
"/wikis?&site=webapps&filter=default",
"/items/excerpt","noInherit,noTruncate,noHeaders")

Labels:

Tweet this | Google+ it | Share on FB

« Home | Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »
| Next »

»

Post a Comment