Question Locked

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

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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!

2 replies

by pcubbage1934

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.

This discussion is closed to new replies.