Formula to match partial text string.

Thanks:  0
Likes:  0

# Thread: Formula to match partial text string.

1. ## Formula to match partial text string.

Have a long list of bond names (full text string I want) and another list that is compiled with essentially the same data, just more of it and unsorted. Is there a formula that is capable of matching the 3/4 text strings I have to the full text strings I have? the 3/4's are not "closest match" they should link up exactly to a full string in other cells.

For example, A1:A2800 = full text strings (columns b, c, d have other data) E1:E500 has the 3/4 text strings that I want to reconcile with the full ones. Want to lookup the full text string, find the 3/4 string tha matches and then have the corresponding data to the 3/4 strings pulled out.

let me know if you need more clarity.

Rob.

2. ## Re: Formula to match patial text string.

It's not clear to me whether you want to look up the 3/4 strings in the table of full strings, or vice versa?

3. ## Re: Formula to match patial text string.

Essentially what I am trying to do is match the 3/4 strings to the appropriate full string. Full strings have the company name i.e., ONT BND 5.15 DEC1/15, while the 3/4 strings only contain BND 5.15 DEC1/15.(for example)

worksheet with full strings has pricing from my bank, 3/4 strings have pricing from an outside dealer. is it possible to reconcile the 3/4 strings to the full strings and then have the output be the row (row across from 3/4 string) that corresponds to the selected 3/4 string?

(hopefully this didn't drive you batty.)

Rob

4. ## Re: Formula to match patial text string.

Maybe I should clarify my objectives... I don't want someone to write me a magnificent formula or anything of the like... I am here for general knowledge, looking for a formula that can match partial text strings to full ones. Is this even possible to do in excel? What would be the best formula to apply here?

5. ## Re: Formula to match patial text string.

If you are looking up the partials against the full strings, then you can use wildcards with MATCH:
=MATCH("*"&E1&"*",\$A\$1:\$A\$1000,0)
to return the matching row number. Does that help?

6. ## Re: Formula to match patial text string.

Just to try and firm this up.. I am trying to find the matching full string based on the partial text string. I think that is what you proposed here, but just in case I thought I would confirm my objectives.

7. ## Re: Formula to match patial text string.

Yep - the formula above will return the row number where your partial string is found in the list of full strings (if there's more than one match, it will return the first one - I don't know if that's an issue).

8. ## Re: Formula to match patial text string.

I am new here, so I hope this POST is okay... I have a follow-up question to this Post:
This is REALLY SUPER, but is there a way to have it return more that just the first one? Can I get a list of rows in which this partial string appears?

9. ## Re: Formula to match patial text string.

You can use a formula like this:

=IF(COUNTIF(\$A\$1:\$A\$8,"*"&\$E\$1&"*")>=ROWS(\$E\$1:\$E1),SMALL(IF(ISNUMBER(SEARCH(\$E\$1,\$A\$1:\$A\$8)),ROW(\$A\$1:\$A\$8),""),ROWS(\$E\$1:\$E1)),"")

where A1:A8 is the data range, E1 contains the text you want to check for, and the formula is entered in row 1. It needs to be array-entered using Ctrl+Shift+Enter rather than just enter as it is an array formula, then fill down until it returns a 'blank' cell

10. ## Re: Formula to match patial text string.

Originally Posted by RoryA
You can use a formula like this:

=IF(COUNTIF(\$A\$1:\$A\$8,"*"&\$E\$1&"*")>=ROWS(\$E\$1:\$E1),SMALL(IF(ISNUMBER(SEARCH(\$E\$1,\$A\$1:\$A\$8)),ROW(\$A\$1:\$A\$8),""),ROWS(\$E\$1:\$E1)),"")

where A1:A8 is the data range, E1 contains the text you want to check for, and the formula is entered in row 1. It needs to be array-entered using Ctrl+Shift+Enter rather than just enter as it is an array formula, then fill down until it returns a 'blank' cell

in the same method, how to get result from A1:E8, instead of A1:A8

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•