Searching for Text string

Vannah

New Member
Joined
Jan 18, 2017
Messages
3
Hi everyone,

I'm a little new to this so please bear with me.

I have an Excel 2010 worksheet with 3 different tabs. One tab is where I have my master database and where all the other tabs get their information from. Tabs 2 and 3 are datasheets from different sources.

Excel 2010 32 bit
A
B
1
TAB 1 - MASTER DATABASE
2
NAMEPROVIDER
3
CAPTAIN AMERICA: CIVIL WARA
4
ADJUSTMENT BUREAU, THEB
5
A PLACE IN THE SUNC
6
BOY NEXT DOOR, THEB
7
BOURNE SUPREMACY, THEA
8
AS ABOVE, SO BELOWC
9
10
TAB 2 - Source X
11
NAMEPROVIDER
12
CAPTAIN AMERICA CIVIL WAR
=INDEX($B$3:$B$8,(MATCH(A12,$A$3:$A$8,0)))​
13
THE ADJUSTMENT BUREAU
=INDEX($B$3:$B$8,(MATCH(A13,$A$3:$A$8,0)))​
14
AS ABOVE SO BELOW
=INDEX($B$3:$B$8,(MATCH(A14,$A$3:$A$8,0)))​
15
16
17
TAB 3 - Source Y
18
NAMEPROVIDER
19
THE BOURNE SUPREMACY
=INDEX($B$3:$B$8,(MATCH(A19,$A$3:$A$8,0)))​
20
CAPTAIN AMERICA: CIVIL WAR=INDEX($B$3:$B$8,(MATCH(A20,$A$3:$A$8,0)))
21
A PLACE IN THE SUN=INDEX($B$3:$B$8,(MATCH(A21,$A$3:$A$8,0)))

<tbody>
</tbody>
Sheet: Sheet1

<tbody>
</tbody>

I would normally just use INDEX MATCH to find the corresponding provider to complete the missing info in the source tabs but my issue is that each source has a potentially different naming convention, which, when using a normal INDEX MATCH formula, will result in an error since the match is not exact.

So what I need is to somehow search not for the entire name but just part of it so I can establish which provider it belongs to. My biggest problems are with "The" and "A" being sometimes at the front or end of a name, commas and other punctuation etc. Can anyone help me do this, please?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
You should delete "A" and "The" from the start and the end of the string, as well as commas, colons etc. then make a comparison.
 

Vannah

New Member
Joined
Jan 18, 2017
Messages
3
Paste in B12
=VLOOKUP(LEFT(SUBSTITUTE(A12,"THE ","",1),8)&"*",$A$3:$B$8,2,0)

Hi There,

Thanks so much for your reply. Can we change the Vlookup (won't work in my case due to left>right constraints) to make it work in an Index/Match scenario?

Thanks again.
 

Vannah

New Member
Joined
Jan 18, 2017
Messages
3

ADVERTISEMENT

You should delete "A" and "The" from the start and the end of the string, as well as commas, colons etc. then make a comparison.

Hi,

Thanks for the idea but, unfortunately, this is not a viable option. Each sheet has an average of 7.000 lines worth of entries. To implement your idea would mean having to do this one by one (or run the risk or a formula getting it wrong) and this is just is not viable.

Thanks anyway, though.
 

wangh3

Board Regular
Joined
Jun 24, 2015
Messages
53
I think your best bet is to create a simple macro to delete out all the punctuations that could be an issue and then remove all spaces between text. Then you should be able to Index match appropriately.
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
Hi,

Thanks for the idea but, unfortunately, this is not a viable option. Each sheet has an average of 7.000 lines worth of entries. To implement your idea would mean having to do this one by one (or run the risk or a formula getting it wrong) and this is just is not viable.

Thanks anyway, though.

Of course I do not mean converting the 7000 strings manually, but using a helper column to perform this with a formula, then selecting the correct name from the database.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,096
Members
415,876
Latest member
csibonga2k17

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
Top