Adding text to a cell based on specific text characters in another cell

Hi, I receive a daily list of around 4k-5k products that up until now is taking me hours to sort.

Attached is a tiny sample of my dilemna. There are 4 columns A and D and 7 rows 2-8.

From the text in range A2:A8 I need to recognise and remove the 'R' at the end of the text in rows 2,4,5,6 - The '-Demo' in row 3,
the '-R' in row 7 and the 'WR' in row 8. and insert the results in the corresponding cells in Column B as shown on the worksheet.

Then i need to have specific text inserted in Column D based on the last 2 characters from the cells in Column C.

The 'C' column range is all 6 digit numbers with 2 letters on the end. These are either 'R4' or 'N1'.

R4 = Refurbished
N1 = New

I have several macros that run within the sheet but I cannot figure this one out, any help would be fantastic

Regards
John