Use compass directions in two columns to get data in a third

[ShaneMellard]

Howdy! In the attached sheet, I'd like to automate the data in f28-33 using what's been input in b28-33 and d28-33. I could use some assistance as I am at my self help limit :)

Thanks,

Shane

[#2 · Accepted solution]

@ShaneMellard Not very elegant, but this could work:

=SWITCH(MID(B28,FIND("'",B28)+2,1)&MID(D28,FIND("'",D28)+2,1),"NE","SW","NW","SE","SE","NW","SW","NE")

[#3]

@Riny_van_Eekelen Thank you very much! 

[#4]

@ShaneMellard 

Here's a formula that works on the basis of the assumption that you always have the North/South orientation in column B, the East/West in Column D. 

=IF(A28="","",(IF(RIGHT(B28,3)="rth","S","N")&IF(RIGHT(D28,4)="East","W","E")))

The outer most IF condition just keeps column F blank if column A is blank.

Beyond that, since "North" and "South" have three distinctive characters as their last three, it's easiest to just look for "rth" or "uth"--again, assuming you always have North or South in that column, and if it finds it's the North, then it reverses to "S"

and concatenates that with a similar IF condition that returns "W" if the word is "East" and vice-versa.

P.S. You appear to have formatted all those cells as "Text" which initially made it hard to enter a formula there. Unless there's some compelling reason to do that, don't. Keep them "General" until forced to change, and then do it cell by cell, or row by row or column by column as needed.