Extraction of integer value from dropdown list inside the same cell?

Question

Using Google Sheets. In one cell, I have a dropdown list that is generated from a concatenation of integer ID column and name column. To get the value of the integer from the selection, I have another cell with this formula:

=LEFT(N1, FIND(" ",N1))

Is there a way that the formula can be used inside the same cell that has the dropdown, without getting a circular dependency error?

Answer 1

This can be done with Google Script. An example is here.

function onEdit(e) {

var ss = e.source;
var s = ss.getSheetByName("Testing");
var r = e.range;

  // to let you modify where the action and move columns are in the form responses sheet
var actionCol = 1;

  // Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();
  // Get the number of columns in the active sheet.
  // -1 to drop our action/status column
  //var colNumber = s.getLastColumn()-1; //not needed because we are limiting the edit to a single row and cell

var curCell = s.getRange(rowIndex,1, 1, 1); //the same cell that has dropdown but can be a different one

if (e.value !== "" && colIndex == actionCol) {
var curCellValue =  curCell.getValue();
var curCellLeft = curCellValue.split(" ");
var val = curCellLeft[0];
//is it a number?
var matches = val.match(/\d+/g);
if (matches != null) {
 curCell.setValue(val);
}
}
}

Answer 2

=iferror(LEFT(N1, FIND(" ",N1)),"")