Taking text in a long column and converting it into corresponding text in another. New at Excel! Vlookup?

[Jwan622]

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?

 

[Pup Denab]

HI Jwan
I will give you the formulas, to do this, I will extract the 2 letters, the other will match the 2 letters with their 6 letter code, I'll assume you data is in columns A & B

Put this formula in column C,
[TABLE="width: 158"]
<colgroup><col width="158"></colgroup><tbody>[TR]
[TD="class: xl65, width: 158"]=VLOOKUP(D1,D:E,2,0)
[/TD]
[/TR]
</tbody>[/TABLE]

Put this formula in column D
[TABLE="width: 96"]
<colgroup><col width="96"></colgroup><tbody>[TR]
[TD="width: 96"]=MID(A1,3,2)
[/TD]
[/TR]
</tbody>[/TABLE]

Put this formula in column E,
[TABLE="width: 38"]
<colgroup><col width="38"></colgroup><tbody>[TR]
[TD="width: 38"]=A1
[/TD]
[/TR]
</tbody>[/TABLE]
you will need to drag these to cover you data (select cell, bottom right hand corner of cell, will be a small square(double click on this, if should autofill the formula)

Columns D & E will create a table, which you can ref from column C (as vlookup only works from left to right)

 

[Jwan622]

thanks. i'll try this out later today!