How does excel extract the community name marked in yellow and assign it to the number set in yellow?

I know what you mean by collocation, that is, Acadia Manor = 3; Anning Street = 4; The yellow column at the back will automatically generate numbers according to the cells in the previous column. If so, unit W2 =if(left(L2, 4)= "Arcadia", 3, if(left(L2, 3)= "Anning Street", 4, IF (see if there are any other communities ...)). Pull it down directly at the back.

It's not troublesome to write only a few communities, but it's troublesome to write more. If there are many, it is suggested to add a column in front to extract the name of the manor. The left side is the same as the company above, and then the right side lists a table of corresponding communities and numbers, which is directly extracted by vlookup.