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 Ty,

Am Thu, 28 Jun 2012 13:00:28 -0700 (PDT) schrieb Ty:

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

system A in E2:E6, System B in C2:C6, then:
=IF(LEN(E2)=15,INDEX($C$2:$C$6,MATCH("*"&MID(E2,11 ,1)&MID(E2,13,1)&"*",$C$2:$C$6,0)),INDEX($C$2:$C$6 ,MATCH("*"&MID(E2,11,4)&"*",$C$2:$C$6,0)))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Ty,

Am Thu, 28 Jun 2012 22:38:08 +0200 schrieb Claus Busch:

system A in E2:E6, System B in C2:C6, then:
=IF(LEN(E2)=15,INDEX($C$2:$C$6,MATCH("*"&MID(E2,11 ,1)&MID(E2,13,1)&"*",$C$2:$C$6,0)),INDEX($C$2:$C$6 ,MATCH("*"&MID(E2,11,4)&"*",$C$2:$C$6,0)))

in the last condition of the formula is a typo.
Try:
=IF(LEN(E2)=15,INDEX($C$2:$C$6,MATCH("*"&MID(E2,11 ,1)&MID(E2,13,1)&"*",$C$2:$C$6,0)),INDEX($C$2:$C$6 ,MATCH("*"&MID(E2,11,3)&"*",$C$2:$C$6,0)))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Jun 28, 3:38*pm, Claus Busch wrote:
Hi Ty,

Am Thu, 28 Jun 2012 13:00:28 -0700 (PDT) schrieb Ty:

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

system A in E2:E6, System B in C2:C6, then:
=IF(LEN(E2)=15,INDEX($C$2:$C$6,MATCH("*"&MID(E2,11 ,1)&MID(E2,13,1)&"*",$C$2*:$C$6,0)),INDEX($C$2:$C$ 6,MATCH("*"&MID(E2,11,4)&"*",$C$2:$C$6,0)))

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks,.. Excellent. This will do the vlookup and all in one cell. I
made some changes to match what I really have after I do a download in
a report. I have about 1300 rows for each system and placed them in
separate sheets. And C2 have System A and E2 is System B. I'm still
trying to figure out what this is doing.

And System B is the new system that has some errors in the column.
Trying to findout how many errors such as other system names listed
and blanks. Other system names such as SCNRALU on the same row as NA-
SMG-SA-RNBAW.


System A c2:c1300 System B e2:e1300
=IF(LEN(C2)=15,INDEX($e$2:$e
$1300,MATCH("*"&MID(c2,11,1)&MID(c2,13,1)&"*",$e$2 :$e$1300,0)),INDEX($e
$2:$e$1300,MATCH("*"&MID(c2,11,4)&"*",$e$2:$e$1300 ,0)))

Sheet for SCNRBU. I inserted Column D-Inserted the formula. I have
#N/A in Column D for the NA-SMG-SA-RNBAW in Col C and SCNRBU in Col
E. This is not correct. It should be a match on Row 2.

Long day here at work. I missed something.

Hi Ty,

Am Thu, 28 Jun 2012 14:28:19 -0700 (PDT) schrieb Ty:


System A c2:c1300 System B e2:e1300
=IF(LEN(C2)=15,INDEX($e$2:$e
$1300,MATCH("*"&MID(c2,11,1)&MID(c2,13,1)&"*",$e$2 :$e$1300,0)),INDEX($e
$2:$e$1300,MATCH("*"&MID(c2,11,4)&"*",$e$2:$e$1300 ,0)))
^^^^^^^^
Sheet for SCNRBU. I inserted Column D-Inserted the formula. I have
#N/A in Column D for the NA-SMG-SA-RNBAW in Col C and SCNRBU in Col
E. This is not correct. It should be a match on Row 2.

have a look to my second answer. In the formula above is a typo.
=IF(LEN(C2)=15,INDEX($e$2:$e$1300,MATCH("*"&MID(c2 ,11,1)&MID(c2,13,1)&"*",$e$2:$e$1300,0)),INDEX($e$ 2:$e$1300,MATCH("*"&MID(c2,11,3)&"*",$e$2:$e$1300, 0)))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Jun 28, 4:36*pm, Claus Busch wrote:
Hi Ty,

Am Thu, 28 Jun 2012 14:28:19 -0700 (PDT) schrieb Ty:



System A c2:c1300 *System B e2:e1300
=IF(LEN(C2)=15,INDEX($e$2:$e
$1300,MATCH("*"&MID(c2,11,1)&MID(c2,13,1)&"*",$e$2 :$e$1300,0)),INDEX($e
$2:$e$1300,MATCH("*"&MID(c2,11,4)&"*",$e$2:$e$1300 ,0)))
* * * * * * * * * * * * * * * * * * * * * * * * * * * * ^^^^^^^^
Sheet for SCNRBU. *I inserted Column D-Inserted the formula. *I have
#N/A in Column D for the NA-SMG-SA-RNBAW in Col C and SCNRBU in Col
E. *This is not correct. *It should be a match on Row 2.

have a look to my second answer. In the formula above is a typo.
*=IF(LEN(C2)=15,INDEX($e$2:$e$1300,MATCH("*"&MID(c 2,11,1)&MID(c2,13,1)&"*",$*e$2:$e$1300,0)),INDEX($ e$2:$e$1300,MATCH("*"&MID(c2,11,3)&"*",$e$2:$e$130 0,*0)))

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I see where you changed the MID(c2,11,4) to MID(c2,11,3). I stepped
through the calculation. It is picking up the "-"(dashes, I think)
and giving me #N/A. I know that SA-RNBAW is a tough one to match up
with SCNRBU in comparison to the RAT to SCNRATU.

It works find if I just do what you listed out for 6 rows but not for
my 1300 rows.

I'm scratching my head on this one.

Thanks,
Ty

On Jun 28, 5:06*pm, Ty wrote:
On Jun 28, 4:36*pm, Claus Busch wrote:





Hi Ty,

Am Thu, 28 Jun 2012 14:28:19 -0700 (PDT) schrieb Ty:

System A c2:c1300 *System B e2:e1300
=IF(LEN(C2)=15,INDEX($e$2:$e
$1300,MATCH("*"&MID(c2,11,1)&MID(c2,13,1)&"*",$e$2 :$e$1300,0)),INDEX($e
$2:$e$1300,MATCH("*"&MID(c2,11,4)&"*",$e$2:$e$1300 ,0)))
* * * * * * * * * * * * * * * * * * * * * * * * * * * * ^^^^^^^^
Sheet for SCNRBU. *I inserted Column D-Inserted the formula. *I have
#N/A in Column D for the NA-SMG-SA-RNBAW in Col C and SCNRBU in Col
E. *This is not correct. *It should be a match on Row 2.

have a look to my second answer. In the formula above is a typo.
*=IF(LEN(C2)=15,INDEX($e$2:$e$1300,MATCH("*"&MID(c 2,11,1)&MID(c2,13,1)&"*",$**e$2:$e$1300,0)),INDEX( $e$2:$e$1300,MATCH("*"&MID(c2,11,3)&"*",$e$2:$e$13 00*,*0)))

Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

I see where you changed the MID(c2,11,4) to MID(c2,11,3). *I stepped
through the calculation. *It is picking up the "-"(dashes, I think)
and giving me #N/A. *I know that SA-RNBAW is a tough one to match up
with SCNRBU in comparison to the RAT to SCNRATU.

It works find if I just do what you listed out for 6 rows but not for
my 1300 rows.

I'm scratching my head on this one.

Thanks,
Ty- Hide quoted text -

- Show quoted text -

Claus or anyone,

I had an extra letter in SMGH. Changed LEN to 16. Increased the MID
by 1. Still having a problem- It will match up with blank cells in
Column E, too. Will continue to troubleshoot tonight. I have to have
this complete in the next 14 hours.

Any assistance will be greatly appreciated.

Thanks,
Ty

Hi Ty,

Am Thu, 28 Jun 2012 20:16:37 -0700 (PDT) schrieb Ty:

I had an extra letter in SMGH. Changed LEN to 16. Increased the MID
by 1. Still having a problem- It will match up with blank cells in
Column E, too. Will continue to troubleshoot tonight. I have to have
this complete in the next 14 hours.

if you have as well SMG and SMGH, try:
=IF(LEN(C2)=15,INDEX($E$2:$E$1300,MATCH("*"&MID(C 2,FIND("#",SUBSTITUTE(C2,"-","#",3))+1,1)&MID(C2,FIND("#",SUBSTITUTE(C2,"-","#",3))+3,1)&"*",$E$2:$E$1300,0)),INDEX($E$2:$E$ 1300,MATCH("*"&MID(C2,FIND("#",SUBSTITUTE(C2,"-","#",3))+1,3)&"*",$E$2:$E$1300,0)))



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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

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