Converting Text

T

Ty

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
 
C

Claus Busch

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
Click to expand...

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
 
C

Claus Busch

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)))
Click to expand...

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
 
T

Ty

Hi Ty,

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



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
Click to expand...

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.
 
C

Claus Busch

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.
Click to expand...

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
 
T

Ty

Hi Ty,

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



                                                        ^^^^^^^^

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
Click to expand...

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
 
T

Ty

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 -
Click to expand...

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
 
C

Claus Busch

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.
Click to expand...

if you have as well SMG and SMGH, try:
=IF(LEN(C2)>=15,INDEX($E$2:$E$1300,MATCH("*"&MID(C2,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
 
V

Vacuum Sealed

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
Click to expand...
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
 
T

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 -
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converting labels to text 1
Word Audio to text? 3
Converting hyperlinks to plain text 2
converting mail message to plain text 1
converting number data to text and formating 2
Converting from text file to excel 1
Converting Numbers into Text (words) 3
Converting RTF to Text 0

Top