google sheets
In order to create some list, I've been copy some data from the web.
Unfortunately it looks like it has been added second row for each record it need.
For example:
Each record is in the same line, and I'll need to have only the first line of each record (only "Alpena Community College" from the record "Alpena Community College
Alpena, MI"
NO - copying google spreadsheet is severly limited and will not copy over YOUR range permission settings but maddeningly then gives everyone edit access - it should at least default to comment access only - its VERY frustrating. So in short the ''copy'' is not a copy.
If anyone discovers that this answer is wrong - PLEASE TELL ME !
With the following little piece of code you can accomplish that.
function myFind() {
var ss = SpreadsheetApp.getActive(), output = [];
var findData = ss.getSheetByName('fr').getDataRange().getValues();
var searchData = ss.getSheetByName('cql').getDataRange().getValues();
for(var i=1, iLen=findData.length; i<iLen; i++) {
for(var j=0, jLen=searchData.length; j<jLen; j++) {
for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
var find = findData[i][0];
if(find == searchData[j][k]) {
output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
}
}
}
}
return output;
}
Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.
find
search
result
I've created an example file for you: get cell row and column index
Best Answer
Assuming you have a new line character in every cell and you want to extract everything before the new line, try:
(where A is the column with the addresses and your data starts in row 2 --> change to suit). After that, you can copy the output and use 'paste special' (values only) to overwrite to original column...