Convert column of text to other text. Help!

[Jwan622]

I have two columns that read:
XXHSXX HR
XXTRXX MG
XXXFXX XG
XXHSXX HS
XXTRXX MG
XXMEXX RU
XXHSXX HS


I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text?

 

[oldbrewer]

for every XXHSXX do you want to return HR ?

 

[Jwan622]

Yes and for every XXTRXX, I want to return an MG. Sorry if that was not clear before. I wasn't sure about the right way to express that.

 

[Rick Rothstein]

I have two columns that read:
XXHSXX HR
XXTRXX MG
XXXFXX XG
XXHSXX HS
XXTRXX MG
XXMEXX RU
XXHSXX HS

I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text?

Please clarify the two red highlighted lines of data... the left column data is identical, but each has a different two-letter code in the right column.

 

[Jwan622]

I would like to edit my original post. Sorry I'm new here and apparently, not very careful either.


I have two columns that read:
XXHSXX HR
XXTRXX MG
XXXFXX XG
XXHSXX HS
XXTRXX MG
XXMEXX RU
XXHSXX HR
XXHSXX
XXTRXX
XXTRXX
....


The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?

 

[oldbrewer]

XXHSXX HR x
XXTRXX MG
XXXFXX XG
XXHSXX HS x
XXTRXX MG
XXMEXX RU
XXHSXX HR x

you still have inconsistencies but anyway tell us the rules for getting the two letters please