I'd like to replace occurrences found in Column A (cells A2:A200) so that if I enter 2
/02
/002
or 343
or 98
/098
it auto-replaces it with UKD002
/UKD343
/UKD098
.
Is this possible?
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityI'd like to replace occurrences found in Column A (cells A2:A200) so that if I enter 2
/02
/002
or 343
or 98
/098
it auto-replaces it with UKD002
/UKD343
/UKD098
.
Is this possible?
From rev.
I'd like to replace occurences found in Column A (Cell A2:A200) so that if I enter 2/02/002 or 343 or 98/098 it auto-replaces it with UKD002/UKD343/UKD098.
To replace a value automatically (just after it was typed) on Google Sheets use an onEdit
, simple or installable, Google Apps Script triggers.
Example:
function onEdit(e){
var autoreplace = {
2/02/002:'UKD002',
343:'UKD343',
98/098:'UKD098'
}
if(autoreplace[e.value]){
e.range.getCell(1,1).setValue(autoreplace[e.value]);
}
}
=ARRAYFORMULA(IFERROR(VLOOKUP($C$2:$C, QUERY({
IF(LEN(A2:A)=1, "UKD00"&A2:A,
IF(LEN(A2:A)=2, "UKD0"&A2:A,
IF(LEN(A2:A)=3, "UKD"&A2:A, A2:A)))},
"select Col1,count(Col1)
where Col1 is not null
group by Col1
label count(Col1)''"), 2, 0)))