formula in next cell

[ericlch16]

i have these in a column. each line is a row

128982 VC AAAA
1389883; MC AAAA
1589 DC BBBB
179024;VC DDDDD
1212313;AM EEEEE
18902 MC FFFFF
18902 FFFFF
3033383; B#111; VC; XXXX
3035383; B#222; DC; YYYY

i want the next column to be

VC
MC
DC
VC
AM
MC
<blank>
VC
DC

any formula i can put in a cell to generate the next column?

 

[schielrn]

Are those the only 4 values it could be in those cells or do you have more and these are just an example?

I don't see any clear cut reason how Excel would know to extract these? It is not always after the first space and there is not always a ; character.

Do you have any information that might distinguish this better?

 

[ericlch16]

Are those the only 4 values it could be in those cells or do you have more and these are just an example?

I don't see any clear cut reason how Excel would know to extract these? It is not always after the first space and there is not always a ; character.

Do you have any information that might distinguish this better?

basically what i want is:

if the cell contains DC returns DC
if the cell contains AM returns AM
if the cell contains MC returns MC
if the cell contains VC returns VC
else return blank


if a cell contain DC and AM .. it does not matter it returns DC.

i try this but not working yet

=IF(ISERROR(FIND("VC",G6055)),"VC",IF(ISERROR(FIND("MC",G6055)),"MC",IF(ISERROR(FIND("AC",G6055)),"AC",IF(ISERROR(FIND("DC",G6055)),"DC",""))))

 

[schielrn]

What happens if you change iserror to isnumber?

 

[Jonmo1]

Also perhaps change FIND to SEARCH..

Find = Case Sensitive (will only find DC, will NOT find Dc or dC or dc)
Search = Not Case Sensitive.

 

[ericlch16]

great!

 

[Jonmo1]

Try

=LOOKUP("z",CHOOSE({1,2},"",LOOKUP(2,1/SEARCH($F$1:$F$4,A1),$F$1:$F$4)))

A1 = Text string
F1:F4 = list of values (VC,MC,DC,AM)

 

[ericlch16]

it works!

=LOOKUP("z",CHOOSE({1,2},"",LOOKUP(2,1/SEARCH(listofcards!$F$1:$F$4,G6055),listofcards!$F$1:$F$4)))

Yeah! i put it in another sheet.

 

[Jonmo1]

Works for me

Excel Workbook
	A	B	C
1	128982 VC AAAA		VC
2	1389883; MC AAAA		MC
3	1589 DC BBBB		DC
4	179024;VC DDDDD		VC
5	1212313;AM EEEEE		AM
6	18902 MC FFFFF		MC
7	18902 FFFFF
8	3033383; B#111; VC; XXXX		VC
9	3035383; B#222; DC; YYYY		DC
Sheet1

Excel Workbook
	F
1	VC
2	MC
3	DC
4	AM
listofcards

 

[Jonmo1]

But I did use SEARCH which is NOT case sensitive, you may want to change search to find.