I'm trying to figure out how to duplicate part of the content of a column (text) to another column in other sheet. For example, I have column A in Sheet1 and I want to show only a word of this column in Sheet2. Here an example file.
2 Answers
Here how to do that with a script.
Code
function myFind(findRange) {
var output = [];
var find = ["Vault", "Drive", "Apps"];
for(var i=0, iLen=findRange.length; i<iLen; i++) {
for(var j=0, jLen=find.length; j<jLen; j++) {
if(findRange[i][0].indexOf(find[j]) !== -1) {
output.push([find[j]]);
break;
}
}
}
return output;
}
Explained
The range is searched by the key words in the find array. Once a word is found, the search is stopped and the next search will commence. You can add more key words in the var find array.
Note
The order of key words is very important. Better is to choose unique key words, like:
- Google Vault
- Google Drive
- Google Apps
All is of course dependent upon the data available.
Example
I've added the script (Tools>Script editor) and the ARRAYFORMULA
version of the solution Punchlinern provided in your example file.
If you only have those three strings to search for (Apps
, Drive
and Vault
), you could use the following function, as suggested by shipr (edited to suit your cause and Google Spreadsheets):
=IF(IFERROR(SEARCH("Vault";DATA!A2);0);"Vault";IF(IFERROR(SEARCH("Drive";DATA!A2);0);"Drive";IF(IFERROR(SEARCH("Apps";DATA!A2);0);"Apps";"None")))
Paste it into the first cell in the TABLE
sheet and copy down.
It's not overly beautiful, you have to add the IFERROR
-part for every word you want to search for, but it works.
-
-
I've just recently started discovering those, so I'm not natively using them yet. What is the benefit of using an
ARRAYFORMULA
in this case? Mar 9, 2014 at 22:27 -
chat.stackexchange.com/transcript/message/14195594#14195594 Mar 10, 2014 at 7:01