Excel auto populate

Question

Excel auto populate

Excel Gott 0

Hi there,

I am trying to organise a spreadsheet where column C will autopopulate with a particular word depending on the information provided in column A.

For example if 'TRT' is part of the text in A2, I would like 'toast' automatically put in C2. If PIP is put in A3, then I would like 'polly' put in C3

There would be 14 different options and this would vary through rows 2 to over 1000

If there a way to do this?

Many thanks

3 answers

Your answer

Answer 1

Ashish Mathur 101.7K Volunteer Moderator

Hi,

In cell B2, enter this formula

=IFERROR(BYROW(A2:A10,LAMBDA(s,ARRAYTOTEXT(FILTER(F2:F3,REGEXTEST(s,E2:E3,1))))),"")

Hope this helps.

User's image

Answer 2

User's image

Hi,

Use below formula in cell F2.

Change the text in column C and also change the substitute text in column D.

Change the range in formula as per you need.

Hope this helps.

IlirU

=LET(
  s, A2:A20, tb, C2:D5,
 tx, TAKE(tb,, 1), sub, DROP(tb,, 1),
len, LEN(s), FILTER(TEXTSPLIT(ARRAYTOTEXT(TRANSPOSE(VSTACK(TEXTSPLIT(TEXTJOIN(";", FALSE,
     MAP(tx, sub, LAMBDA(a,b, ARRAYTOTEXT(IF(len - LEN(SUBSTITUTE(s, a, "")) = 0, "", b))))), ", ", ";"),
     IF(BYCOL(ISNUMBER(--TEXTSPLIT(TEXTJOIN(";", FALSE,
     MAP(tx, sub, LAMBDA(a,b, ARRAYTOTEXT(IF(len - LEN(SUBSTITUTE(s, a, "")) = 0, 0, b))))), ", ", ";")), AND),
     "-", "")))),, ", ", TRUE), s <> "")
)

Answer 3

Use a formula in column C that checks the text in column A and returns the corresponding word. For partial-text matches like “TRT” inside a longer string, use SEARCH or FIND inside IF (or IFS).

Example for a few codes (extend pattern for all 14):

=IFERROR(
    IF(SEARCH("TRT",A2),"toast",
    IF(SEARCH("PIP",A2),"polly",
    "")),
"")

However, SEARCH returns an error if the text is not found, so a more robust pattern for many options is:

=IFS(
    ISNUMBER(SEARCH("TRT",A2)), "toast",
    ISNUMBER(SEARCH("PIP",A2)), "polly",
    ISNUMBER(SEARCH("ABC",A2)), "word3",
    ISNUMBER(SEARCH("XYZ",A2)), "word4"
    /* continue for all 14 options */
)

Then fill the formula down from C2 to cover all rows (e.g., to C1000+). When the text in column A changes, column C updates automatically.

If exact matches are needed (A2 equals “TRT”, not just contains), replace ISNUMBER(SEARCH("TRT",A2)) with A2="TRT" in the IFS conditions.

References:

Use Formula AutoComplete

Share via

Excel auto populate

3 answers

Your answer