On Jun 29, 7:15*am, Vacuum Sealed wrote:
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- Hide quoted text -

- Show quoted text -

Thank You! Claus,

I thought you were trying to do the Vlookup and Text conversion. I
spent 1 hour troubleshooting wandering why blanks were still
displaying. I can make this work now. My time has been extended. I
have 30 minutes. 2 pm cst. I will do another column with a vlookup.

Vacuum, I might give it a try but I think I have it now.

Regards,
Ty