Search within a string

J

Jeffrey Green

Well-known Member
Joined
Oct 24, 2007
Messages
1,021
I have part numbers that have a prefix, base and suffix.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
Example <o:p></o:p>
<TABLE style="WIDTH: 123pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=164><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 123pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: silver 1pt solid; BORDER-RIGHT: silver 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid silver .5pt" width=164>YL8Z-2B485-AA<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
However, our Oracle tables output them without dashes, so we get <o:p></o:p>
<TABLE style="WIDTH: 123pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=164><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: silver 1pt solid; BORDER-LEFT: silver 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 123pt; PADDING-RIGHT: 0in; HEIGHT: 12.75pt; BORDER-TOP: silver 1pt solid; BORDER-RIGHT: silver 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid silver .5pt" width=164>YL8Z2B485AA<o:p></o:p>
</TD></TR></TBODY></TABLE>
<o:p></o:p>
The prefixes, for my purposes, are always 4 characters long, so I could strip them out, but the base and suffix are not fixed character lengths. The base could have 4, 5, 6 or more characters and the suffix could easily have 2, 3, 4 or even 5 characters.<o:p></o:p>
<o:p></o:p>
What I need to do is compare two lists. One list has about 2000 parts. My second list has about 5 parts. I need to examine JUST THE BASE piece of the part numbers. But both lists are compressed.<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
I am thinking I need to remove the first four characters using MID function, but then I get lost on how to compare an unknown text string length to another unknown length field.<o:p></o:p>
<o:p></o:p>
Make sense?<o:p></o:p>
<o:p></o:p>
Thanks <o:p></o:p>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
However, our Oracle tables output them without dashes
Click to expand...
but the base and suffix are not fixed character lengths. The base could have 4, 5, 6 or more characters and the suffix could easily have 2, 3, 4 or even 5 characters.
Click to expand...
I need to examine JUST THE BASE piece of the part numbers. But both lists are compressed.
Click to expand...
Based on what you have said here, I fear this would appear to be impossible.

If you do not have dashes, and the base and suffixes can be different lengths, how you can determine where the base stops and the suffix starts for ANY record?

Basically, in programming, you need to define your rules and program from that. You cannot program to something that you can't even define.
 
Upvote 0
Interesting question. How about the following? Assuming your Oracle data is set out like this:

<center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Book1</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: xl2000 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb755237"><input *******="window.clipboardData.setData("Text",document.formFb078704.sltNb935705.value);" value="Copy Formula" name="btCb873980" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb078704"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb078704.txbFb426622.value = document.formFb078704.sltNb935705.value" name="sltNb935705"><option value="=RIGHT(A2,LEN(A2)-4)" selected="selected">B2</option><option value="=RIGHT(A3,LEN(A3)-4)">B3</option><option value="=RIGHT(A4,LEN(A4)-4)">B4</option><option value="=RIGHT(A5,LEN(A5)-4)">B5</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="=RIGHT(A2,LEN(A2)-4)" name="txbFb426622"></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="middle" width="2%">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>A</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>B</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>C</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>D</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>1</center></td><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Oracle Code</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Match String</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>2</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z2B485AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2B485AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>3</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z2D485AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2D485AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>4</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z2D585AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2D585AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>5</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z1D585AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">1D585AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); width: 120pt; background-color: rgb(255, 255, 255);" align="left">Oracle</td><td>
</td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>

You can extract the last n-4 characters using the formula in column B:
=RIGHT(A2,LEN(A2)-4)

then in your lookup sheet, you can use this to try and find data that matches:

<center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Book1</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: xl2000 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb605117"><input *******="window.clipboardData.setData("Text",document.formFb202339.sltNb447362.value);" value="Copy Formula" name="btCb942116" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb202339"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb202339.txbFb150492.value = document.formFb202339.sltNb447362.value" name="sltNb447362"><option value="=MID(A2,6,FIND("-",A2,6)-6)" selected="selected">B2</option><option value="{=MAX(IF(B2=LEFT(Oracle!B$2:B$2001,LEN(B2)),ROW(Oracle!B$2:B$2001),0))}">C2</option><option value="=MID(A3,6,FIND("-",A3,6)-6)">B3</option><option value="{=MAX(IF(B3=LEFT(Oracle!B$2:B$2001,LEN(B3)),ROW(Oracle!B$2:B$2001),0))}">C3</option><option value="=MID(A4,6,FIND("-",A4,6)-6)">B4</option><option value="{=MAX(IF(B4=LEFT(Oracle!B$2:B$2001,LEN(B4)),ROW(Oracle!B$2:B$2001),0))}">C4</option><option value="=MID(A5,6,FIND("-",A5,6)-6)">B5</option><option value="{=MAX(IF(B5=LEFT(Oracle!B$2:B$2001,LEN(B5)),ROW(Oracle!B$2:B$2001),0))}">C5</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="=MID(A2,6,FIND("-",A2,6)-6)" name="txbFb150492"></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="middle" width="2%">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>A</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>B</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>C</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>D</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>1</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-width: 0.5pt; border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Part Number</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Base</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Match Row</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>2</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z-2B485-AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2B485</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>3</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z-2F485-AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2F485</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">0</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>4</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z-2D485-AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2D485</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">3</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>5</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z-2D585--AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2D585</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">4</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;"> </td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); width: 120pt; background-color: rgb(255, 255, 255);" align="left">Check</td><td> </td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

You can extract the base value in column B:
=MID(A2,6,FIND("-",A2,6)-6)

and try to find a row that matches in column C:
=MAX(IF(B2=LEFT(Oracle!B$2:B$2001, LEN(B2)), ROW(Oracle!B$2:B$2001), 0))

The formula in column C is an array formula so please beware that after you have entered it (but before you press the Enter key), you need to confirm it by pressing and holding the Ctrl and Shift keys and then press the Enter key. This will convert it to an array formula - you will know if you have done this correctly because curly brackets {} will automatically appear around the formula. If it cannot find a match it returns the value 0. If it does find a match it gives the row number of the last match.

Andrew
​
</center>
 
Upvote 0
And if you want to go the other way.....assuming your lookup values are like this:

<center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Book1</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: xl2000 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb285793"><input *******="window.clipboardData.setData("Text",document.formFb506491.sltNb910359.value);" value="Copy Formula" name="btCb963142" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb506491"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb506491.txbFb548867.value = document.formFb506491.sltNb910359.value" name="sltNb910359"><option value="=MID(A2,6,FIND("-",A2,6)-6)" selected="selected">B2</option><option value="{=MAX(IF(B2=LEFT(Oracle!B$2:B$2001,LEN(B2)),ROW(Oracle!B$2:B$2001),0))}">C2</option><option value="=MID(A3,6,FIND("-",A3,6)-6)">B3</option><option value="{=MAX(IF(B3=LEFT(Oracle!B$2:B$2001,LEN(B3)),ROW(Oracle!B$2:B$2001),0))}">C3</option><option value="=MID(A4,6,FIND("-",A4,6)-6)">B4</option><option value="{=MAX(IF(B4=LEFT(Oracle!B$2:B$2001,LEN(B4)),ROW(Oracle!B$2:B$2001),0))}">C4</option><option value="=MID(A5,6,FIND("-",A5,6)-6)">B5</option><option value="{=MAX(IF(B5=LEFT(Oracle!B$2:B$2001,LEN(B5)),ROW(Oracle!B$2:B$2001),0))}">C5</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="=MID(A2,6,FIND("-",A2,6)-6)" name="txbFb548867"></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="middle" width="2%">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>A</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>B</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>C</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>D</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>1</center></td><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Part Number</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Base</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Match Row</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>2</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z-2B485-AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2B485</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">6</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>3</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z-2F485-AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2F485</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">0</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>4</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z-2D485-AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2D485</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">3</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>5</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z-2D585--AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2D585</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">4</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); width: 120pt; background-color: rgb(255, 255, 255);" align="left">Check</td><td>
</td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Then your Oracle sheet would look like this:

<center><table align="center" cellpadding="0" cellspacing="0"><tbody><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="#0c266b"><table align="center" border="0" width="100%"><tbody><tr><td align="left">Microsoft Excel - Book1</td><td style="font-size: 9pt; color: rgb(255, 255, 255); font-family: caption;" align="right">___Running: xl2000 : OS = Windows XP </td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); height: 25px;" colspan="5" bgcolor="#d4d0c8"><table valign="MIDDLE" align="center" border="0" width="100%"><tbody><tr><td style="font-size: 10pt; color: rgb(0, 0, 0); font-family: caption;">(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout</td><td align="right" valign="center"><form name="formCb264683"><input *******="window.clipboardData.setData("Text",document.formFb321525.sltNb671829.value);" value="Copy Formula" name="btCb384167" type="button"></form></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0);" colspan="5" bgcolor="white"><table border="0"><tbody><tr><form name="formFb321525"></form><td style="width: 60px;" align="middle" bgcolor="white"><select onchange="document.formFb321525.txbFb007341.value = document.formFb321525.sltNb671829.value" name="sltNb671829"><option value="=RIGHT(A2,LEN(A2)-4)" selected="selected">B2</option><option value="{=MAX(IF(LEFT(B2,LEN(Check!$B$2:$B$5))=Check!$B$2:$B$5,ROW(Check!$B$2:$B$5),0))}">C2</option><option value="=RIGHT(A3,LEN(A3)-4)">B3</option><option value="{=MAX(IF(LEFT(B3,LEN(Check!$B$2:$B$5))=Check!$B$2:$B$5,ROW(Check!$B$2:$B$5),0))}">C3</option><option value="=RIGHT(A4,LEN(A4)-4)">B4</option><option value="{=MAX(IF(LEFT(B4,LEN(Check!$B$2:$B$5))=Check!$B$2:$B$5,ROW(Check!$B$2:$B$5),0))}">C4</option><option value="=RIGHT(A5,LEN(A5)-4)">B5</option><option value="{=MAX(IF(LEFT(B5,LEN(Check!$B$2:$B$5))=Check!$B$2:$B$5,ROW(Check!$B$2:$B$5),0))}">C5</option><option value="=RIGHT(A6,LEN(A6)-4)">B6</option><option value="{=MAX(IF(LEFT(B6,LEN(Check!$B$2:$B$5))=Check!$B$2:$B$5,ROW(Check!$B$2:$B$5),0))}">C6</option></select></td><td align="right" bgcolor="#d4d0c8" width="3%">=</td><td align="left" bgcolor="white"><input size="80" value="=RIGHT(A2,LEN(A2)-4)" name="txbFb007341"></td></tr></tbody></table></td></tr><tr><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); border-left: 0.5pt solid rgb(0, 0, 0); background-color: rgb(212, 208, 200);" align="middle" width="2%">
</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>A</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>B</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>C</center></td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; color: black; font-family: menu; background-color: rgb(212, 208, 200);" align="middle"><center>D</center></td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>1</center></td><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(0, 0, 0) rgb(212, 208, 200) rgb(212, 208, 200) rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Oracle Code</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Match String</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">Row Match</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>2</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z2B485AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2B485AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>3</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z2D485AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2D485AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">4</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>4</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z2D585AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2D585AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">5</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>5</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Z1D585AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">1D585AA</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">0</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-top: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); font-family: menu; background-color: rgb(212, 208, 200);" align="middle" width="2%"><center>6</center></td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; border-left: 0.5pt solid rgb(0, 0, 0); color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">YL8Y2B485AB</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: left;">2B485AB</td><td style="border-right: 0.5pt solid rgb(212, 208, 200); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">2</td><td style="border-right: 0.5pt solid rgb(0, 0, 0); font-size: 10pt; vertical-align: bottom; color: rgb(0, 0, 0); border-bottom: 0.5pt solid rgb(212, 208, 200); font-family: Arial; background-color: rgb(255, 255, 255); text-align: right;">
</td></tr><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); background-color: rgb(212, 208, 200);" colspan="5"><table valign="TOP" align="left" width="100%"><tbody><tr><td style="border-width: 0.5pt; border-style: solid; border-color: rgb(128, 128, 128) rgb(0, 0, 0) rgb(0, 0, 0); width: 120pt; background-color: rgb(255, 255, 255);" align="left">Oracle</td><td>
</td></tr></tbody></table></td></tr></tbody></table>
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.</center>
Everything is the same as before except the formula in column C on the Oracle sheet is:
=MAX(IF(LEFT(B2, LEN(Check!$B$2:$B$5))=Check!$B$2:$B$5, ROW(Check!$B$2:$B$5), 0))

Again this is any array formula which needs to be confirmed using Ctrl + Shift + Enter.

Andrew
​
</center>
 
Upvote 0
Here is an example of the potential risk you are dealing with that I was alluding too previously.

Using your example, let's say that you have two similar records in Oracle that look something like your original example:
YL8Z-2B485-AA
YL8Z-2B48-5AA

They have two distinctly different bases, one has base 2B485, and the other has base 2B48. Once you remove the dashes, both entries will look like: YL8Z2B485AA

So, if you just had YL8Z2B485AA, you couldn't tell if the base is 2B485 or 2B48. Without those dashes, or some "hard and fast rule", you just can't tell which one it was before the dashes were removed.

If none of your "different" entries are remotely similar, you might be able to get away with doing it the way Andrew suggested.
 
Upvote 0
If you have a list of hyphenated Part Numbers, you could match the Oracle imput to that list without VBA.

Select a cell in row 1 and define these names
Name: PartsList RefersTo: =Sheet1!$K$1:$K$11
Name: PartPostfixes RefersTo: =MID(PartsList, 6, 255)

Name: OneToN
RefersTo: =COLUMN(INDEX(Sheet1!$2:$2,1,1):INDEX(Sheet1!1:1,1,LEN(Sheet1!$C1)))

From the Oracle input in A2,
putting =MID(A2,5,255) in B2 strips off the prefix
and the formula in D2 puts the hyphen in the right place
=REPLACE(B2, SUMPRODUCT(ISNUMBER(MATCH(REPLACE(B2,OneToN,0,"-"),PartPostfixes, 0))*OneToN), 0, "-")
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
622
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
Artik
  • Question Question
VBA code to click Image in WebBrowser control.
Replies
4
Views
306
Artik
Artik
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,574
Messages
6,179,629
Members
452,933
Latest member
patv

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top