Display Text Based On Text In Another Column

[Nelz_]

I need help on a formula that can detect 'letters' from column 1 (Trailer no.) and associate it with a list in column 2 (Area) which would then input 'area' into column 3 instead of manually inputting.

My issue is with the Trailer numbers which are as follows:

• 'FF' OR 'R' = Area A 
• 'FFS' OR 'RS' = Area B
• 'FRP' = Area C
• 'B' = Area D
• 'G' = Area E

I've attached a excel sheet to better explain what I'm asking.

Apologies for any confusion!!

[#2]

@Nelz_ 

First, you have to create a table manually to associate the trailers with the areas. Then, you could use a lookup function to look the trailer numbers up in your table and return the area. See attached for an example.

[#3]

@JMB17 

Its close to what I need. Sorry I didn't exactly explain it correctly now that I look at it.

The reason I wanted a letter detection is because the trailers could range from FF1 - FF22 or could have a letter FF1a, RS1a etc which is why I was wondering if there would be a formula to detect just the first couple letters of the trailers?

Hopefully this makes sense 

[#4 · Accepted solution]

@Nelz_ 

My fault, I missed that your first post did not have the trailing numbers. It appears that you want the characters to the left of the first number that appears in the trailer number?

I believe you will still need a lookup table to crosswalk the codes and the places, but I modified the formula to find the first numeric character in the trailer number, if any, and pull the characters to the left of that.

Is the attached workbook what you are trying to do? Note the formula generally will require you to  hit Ctrl+Shift+Enter after keying it in (I don't know if the latest office 365 would as I don't have that version).

[#5]

Thank you !! Exactly what I was after !!

[#6]

I'm glad to hear that worked for you.