Lookup to return cell with similar consecutive characters

G

Grepees

Board Regular
Joined
Apr 15, 2009
Messages
81
Hello Good folks out there,

Is there a lookup that can return cells that contain same consecutive string of characters? I am preparing a report out of a data download that is not that "clean". Let me explain;

<TABLE style="WIDTH: 351pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=468 x:str><COLGROUP><COL style="WIDTH: 218pt; mso-width-source: userset; mso-width-alt: 10642" width=291><COL style="WIDTH: 31pt; mso-width-source: userset; mso-width-alt: 1499" width=41><COL style="WIDTH: 102pt; mso-width-source: userset; mso-width-alt: 4973" width=136><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 218pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 height=17 width=291>Transaction Description</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 31pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 width=41></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 102pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl25 width=136>Employee Names</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Ampah, John Mangers Meeting Tuscon</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Ampah, John K</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Philips Kwa Joint sales Vis Yellow Cab/Bell cab</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Philips, Kwande Anne</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Antelope/Jennifer May Statement</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Antelope, Jennifer P</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl24 height=17>Danida/Davita Site Visi</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Danida, Davida Maria</TD></TR></TBODY></TABLE>

Is there a formula that can look at the transaction descritions on the left (cell A2) and return the names on the right (D2)

I am dealing with like 10K rows of data so its not feasible to go through each row to copy and paste the names. Eg. A5 has characters that appear in the same order as D5 (Danida). How can i write a formula to look at A5 and return D5. Thank you very much in advance.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
The surnames are not always separated by a comma or Forward slash. Another example is this eg. "Champ Richard Business E Super Class Resort". and the name is Champ, Richard.
 
Upvote 0
Hello Good folks out there,

Is there a lookup that can return cells that contain same consecutive string of characters? I am preparing a report out of a data download that is not that "clean". Let me explain;<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<TABLE style="WIDTH: 351pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=468><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 218pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=291>Transaction Description<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 31pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=41><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; WIDTH: 102pt; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm" width=136>Employee Names<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">Ampah, John Mangers Meeting Tuscon<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">Ampah, John K<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">Philips Kwa Joint sales <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">Vis</st1:place> Yellow Cab/Bell cab<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">Philips, Kwande Anne<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">Antelope/Jennifer May Statement<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">Antelope, Jennifer P<o:p></o:p>
</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">Danida/Davita Site Visi<o:p></o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm"><o:p> </o:p>
</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; PADDING-BOTTOM: 0cm; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0cm; PADDING-RIGHT: 0cm; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; PADDING-TOP: 0cm">Danida, Davida Maria<o:p></o:p>
</TD></TR></TBODY></TABLE>

Is there a formula that can look at the transaction descritions on the left (cell A2) and return the names on the right (D2)

I am dealing with like 10K rows of data so its not feasible to go
 
Upvote 0
Grepees said:
Hello Good folks out there,

Is there a lookup that can return cells that contain same consecutive string of characters? I am preparing a report out of a data download that is not that "clean". Let me explain;<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<TABLE class=MsoNormalTable style="WIDTH: 351pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0cm 0cm 0cm 0cm" cellSpacing=0 cellPadding=0 width=468 border=0><TBODY><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 218pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=291>Transaction Description<o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 31pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=41><o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; WIDTH: 102pt; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=136>Employee Names<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 1"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Ampah, John Mangers Meeting Tuscon<o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"><o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Ampah, John K<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 2"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Philips Kwa Joint sales <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on">Vis</st1:place> Yellow Cab/Bell cab<o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"><o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Philips, Kwande Anne<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 3"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Antelope/Jennifer May Statement<o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"><o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Antelope, Jennifer P<o:p></o:p>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-yfti-irow: 4; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Danida/Davita Site Visi<o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent"><o:p></o:p>

</TD><TD style="BORDER-RIGHT: #ece9d8; PADDING-RIGHT: 0cm; BORDER-TOP: #ece9d8; PADDING-LEFT: 0cm; PADDING-BOTTOM: 0cm; BORDER-LEFT: #ece9d8; PADDING-TOP: 0cm; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent">Danida, Davida Maria<o:p></o:p>

</TD></TR></TBODY></TABLE>

Is there a formula that can look at the transaction descritions on the left (cell A2) and return the names on the right (D2)

I am dealing with like 10K rows of data so its not feasible to go
Click to expand...
Maybe this:

http://www.mrexcel.com/forum/showthread.php?t=195635&highlight=fuzzy+match
 
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
617
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
920
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,521
Messages
6,179,280
Members
452,902
Latest member
Knuddeluff

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