Need help with a complicated Concatenation and Abbreviation Macro

[rgrocks]

Help I'm working on my very first VBA Macro. I'm trying to figure out how to create a concatenated name but the data isnt very uniform. I took a 6 hour VBA Excel Macro class but I seem to be out of my depth for this.
Currently the code only works on the cell I have highlighted which is fine for my purposes, but something cleaner and more universal might be nice.
My class didnt cover anything like I want to do "/

Attached is a sample data
VBA Sample to Share.xlsx


I want to put three columns (2 of them will be modified) together to form a new name


The names will look like the following examples




&Division&_&CityAbrv&_R&3DigitAssetNo


CC_HST_PH48
SO_HLB_R050
SO_HLB_RR52
SO_WND_R086
ST_JKN_RM03




My Criteria


1) Division is taken straight from the division column
2) CityAbrv needs to be transformed from the "City" Column and abreviated using an attached list of city abreviations on the worksheet "Official City Code"
3) The last 3 characters of the Asset No. The issue with this is that the Asset No. isnt exactly uniform data I want it to take the last 3 characters.
b) if there is a "-" or " " within the last 3 characters, ignore it and dont use it but instead move on to the next charcter to the left for example, DR H 23 should just be H23
4) There is existing data that I dont want to overwrite so this macro needs to only run on the empty cells on the column I'm modifying.
5) Repeat for the remaining blank cells that have data to concatenate


Here is the code I have so far

Sub SCADALocationName()


 ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],""_"",RC[-1],""_R"",RIGHT(RC[-2],3))"


End Sub

Please, I understand you may not have the time to help me write it but if you could just point me to the syntax I would need and similar macro's so I could disect it for my purposes. Just point me towards specific lessons and I could try to figure it out. Thanks