How can I automatically add text to a cell in Numbers app?

The answer might depend on what you do with the results. Here is one way to do it. Below is the setup. In the end it will look like what you posted.

Column A has all of the serial numbers pre-filled into the cells

Formula in C2 =IF(B2="","",A2)

Fill down to complete the column

Select all of column A except the header cell

Create the highlighting rule shown in the screenshot where the text opacity will be 0% if the text is not the same as in C2. This will create a rule for all of them where each compares to cell C in its own row.

Hide column C

You can try a something like this:

In A2, filled down:

=IF(ISBLANK(B2),"","SN"&RIGHT("000"&ROW()−1,4))

If the cell in column B is blank then the formula inserts a null value.

If the cell in column B is not blank then formula inserts "SN" followed by the relevant number of leading 0s followed by the row number minus 1.

SG

SGIII, Starting from your excellent suggestion I tried this variety and it works

IF(B2≠ISBLANK(cell);”SN000"&ROW(reference)−1;"" )