Google Sheets change value of text in a cell using Query?

1
votes

I'd like to be able to change the value of cell, from a number to text - but I'm not sure how to do it?

I have two tabs, Source and Heath Hill.

Heath Hill uses Query to pull in specific cells from the source tab. Here's my formula:

=QUERY(Source!$1:$1000,"select A,AB, D,F where C = 'Heath Hill'",1)

Columns C and D in Heath Hill are numbers, I'd like to be able to replace them with text.

Column C

  • Replace any number 1 with Business Interest
  • Replace any number 2 with Representing an interest group
  • Replace any number 3 with Profession

Column D

  • Replace any number 1 with Agree
  • Replace any number 2 with Disagree
  • Replace any number 3 with Don't know

Here's a link to my example Google Sheet I've simplified it.

2
google-sheets

2 Answers

0
votes

Solution

To achive what you are aiming here you will need to use an if function.

The formulas you would need to use are as follow:

For column C : =IF(C2=1,"Business Interest",IF(C2=2,"Representing an interest group","Profession"))

For column D : =IF(C2=1,"Agree",IF(C2=2,"Disagree","Don't know"))

Here is an example of this implementation. I have created a new column for making it easier to visualise and the I just dragged and dropped to include all the values.

enter image description here

I hope this has helped you. Let me know if you need anything else or if you did not understood something. :)

0
votes

Its better to use App Script instead

function UpdateReplace() {
  var spreadsheet = SpreadsheetApp.openById('Sheet ID');
  var doc = spreadsheet.getSheetByName('Sheet_Name');
  var range = doc.getRange("C2:C");  
  var data  = range.getValues();
  for (var row = 0; row < data.length; row++) {
    for (var col = 0; col < data[row].length; col++) {
      data[row][col] = (data[row][col]).toString().replace('1', 'Business Interest');
      data[row][col] = (data[row][col]).toString().replace('2', 'Representing an interest group');
      data[row][col] = (data[row][col]).toString().replace('3', 'Profession');
    }
  }
 range.setValues(data);
 Var range1 = doc.getRange("D2:D");
 ​Var data1 = range1.getValues();
  for (var row = 0; row < data.length; row++) {
    for (var col = 0; col < data[row].length; col++) {
 data1[row][col] = (data1[row][col]).toString().replace('1', 'Agree');
 data1[row][col] = (data1[row][col]).toString().replace('2', 'Disagree');
 data1[row][col] = (data1[row][col]).toString().replace('2', 'Don't know'); 
    }
  }
  range1.setValues(data1);
};