On 29/06/2012 6:00 AM, Ty wrote:
Here at the University, I am trying to convert some text in system A
to match another version of the text in another system B so I can just
do a simple vlookup. I just need to convert System A.

System A
NA-SMG-SA-CARDS
NA-SMG-SA-EBZ
NA-SMG-SA-RAL
NA-SMG-SA-RAT
NA-SMG-SA-RNBAW

Sytem B
SCNCRDU
SCNEBZU
SCNRALU
SCNRATU
SCNRBU

I have 4 formulas for CARDS in 4 cells before i decided to post. All
I have is CRD. I need converted to SCNCRDU. So, I have to add the
"U" and the "SCN". I know there has to be a better way than what I am
doing.

=FIND("-",E2,4)
=RIGHT(E2,D2-3)
=SUBSTITUTE(C2,"A","")
=LEFT(B2, LEN(B2)-1)

Any help will be appreciated.

Thanks,
Ty

Hi

If the Values in Column E of System A are just those of the 5 you have
shown then maybe something in a VBA format instead maybe quicker and
cleaner as you do not have to rely on complex nested formulas.

I have tested this locally within a workbook, though it will need the
changed to include External references for System B. Anyway, someone may
be able to clean it up another level.

HTH
Mick.

Sub Convert_Text()

For i = 2 To 6

aRng = Cells(i, 5).Value

For j = i To i

Select Case aRng
Case "NA-SMG-SA-CARDS"
Sheets("System B").Range("C" & j).Value = "SCNCRDU"

Case "NA-SMG-SA-EBZ"
Sheets("System B").Range("C" & j).Value = "SCNEBZU"

Case "NA-SMG-SA-RAL"
Sheets("System B").Range("C" & j).Value = "SCNRALU"

Case "NA-SMG-SA-RAT"
Sheets("System B").Range("C" & j).Value = "SCNRATU"

Case "NA-SMG-SA-RNBAW"
Sheets("System B").Range("C" & j).Value = "SCNRBU"

Case Else: Exit Sub

End Select
Next
Next

End Sub