0

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?

0

=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)))

0

demo spreadsheet

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.