How to ask Excel to pick out an element in a cell value

By J H Frank ·
I tried to do the following:

Cell A1: tax-aw / phone-jh / PC-oy

In Cell B1, I would like Excel to do the following:

whenever Cell A1 has "aw" element,show text"Alan", when Cell A1 has "jh" element, show text "JHao", and so on.

Please can someone help me with this?

Many thanks indeed!

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

It all depends on how general you need to be.

by pcubbage1934 In reply to How to ask Excel to pick ...

Cell A1: tax-aw / phone-jh / PC-oy
Do the initials always follow a - ? Are they always in the same place? Are there always three entries? If so, make a lookup table in alpha sequence for the initials in c1:d3
aw Alan
jh JHao
oy Oliver
The code in B1 is ="tax-"&VLOOKUP(MID(A1,5,2),C1:D3,2)&" / Phone-"&VLOOKUP(MID(A1,16,2),C1:D3,2)&" / PC-"&VLOOKUP(MID(A1,25,2),C1:D3,2)

The result will be
tax-Alan / Phone-Jhao / PC-Oliver

If the position or # of initials varies, then you need to write a more general VB procedure.

Related Discussions

Related Forums