ID Codes

[Pinda]

I have 4 separate ID Codes,

GDMPE...
GDMPA...
GDMPO...
GDMPP...

all with numbers at the end of them (GDMPE1,GPMPA3 etc..)

I want to create a function (maybe an IF) that looks at
the code's last 2 letters and enters a text string in the
cell.

eg. GDMPE to enter People Environment.

IS this possible? Please advise I would be very grateful.

Pinda

 

[Akshay Bakhai]

To extract the 4th and 5th character from the string, use
the MID function which works as follows:
=MID(A1, 4, 2) which means take contents of cell A1 and
starting from 4th position give me 2 characters.

Using this you have your PE, PA, PO, PP etc. Now, you can
use an IF condition to put in the desired labels.

For example: IF(MID(A1, 4, 2) = "PE", "People
Environment", ) etc.

However, you will notice that the IF will start getting
long and complicated to read with 4 conditions. Thus, I
would advice instead to use a lookup table.

Somewhere on an unused portion of your sheet you may key
in the acronyms and their full forms. Let's name this
range MYTABLE.

Now, your formula would become as simple as

=VLOOKUP(MID(A1, 4, 2), MYTABLE, 2, FALSE)

 

[Pinda]

Cheers mate, that works!

Yea the vlookup is alot better because i can update
mytable with more entries later and the formula will be
updated automatically.

Thanks for that, much appreciated.