Find & Replace with Open Cells for New Entries & Column Sorting

Friends,

I hope everyone is well and happy.

Now, this thread has been partially solved previously:

Find string of text within a cell and then replace the cell with new text
https://www.excelforum.com/excel-gen...-new-text.html

I would like to extend my query a bit more in depth.

Just to briefly recap the situation, the purpose is to find a sub-partial text in a column but then return another cell text related to the found cell.

There are two new queries that I would like to request.

First, I would like the end result column to be auto sorted alphabetically in column D.

Second, the column of find text, column B, should extend and create on its own new entries once there are new entries added to the main column in column A.

If there are new entries found, I will update column C what the replacement should be in the end result column.

Please see attached the file for better understanding.