Jan 13 2021 07:47 PM
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:
I've attached a excel sheet to better explain what I'm asking.
Apologies for any confusion!!
Jan 13 2021 08:08 PM
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.
Jan 13 2021 08:19 PM
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
Jan 13 2021 08:38 PM
Solution
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).
Jan 13 2021 11:09 PM
Jan 13 2021 11:26 PM
Jan 13 2021 08:38 PM
Solution
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).