I'm stuck. I have a cell (B2) that is a concatenation of 5 other cells (contains numbers, letters, symbols, etc). I have a column (A) that contains a list of thousands of account numbers. I want to compare the contents of the cell to the entire column to see if it contains any of the account numbers listed in the Column A. Anyone have any ideas?

Do you need to compare the whole value in cell B2, or a string within B2?

I need it to look at everything within B2 to see if any of the loan numbers are buried in it. B2 - no rhyme or reason to the structure of this cell because I'm pulling in free-form comments that were added to the account.

If it's the whole value, try this:

1. insert a column between A and B (you can hide it later). Your lookup field is now C2
2. add a text field like "match" to the new field for every record
3. assign a name to the range for columns A and the new B (for instance, "account")
4. add another new column, d, for the results of your match
5. use vlookup formula in cell d2:
=VLOOKUP(C2,match,2,FALSE)

If you need more help, feel free to pm me

Oops, just saw your reply. It will be a little more complicated...

See if this site helps:

http://www.mrexcel.com/forum/showthread.php?p=955137

Thanks - I'll give it a shot.

cowboys,

is your concatenation a fixed number of characters?

It would be easy to strip out the account number portion and then run the vlookup

Unfortunately it's not.

try

IF(ISNUMBER(SEARCH("*YOUR ACCOUNT NUMBER*",A1)),"YOUR ACCOUNT NUMBER","")


That will do the following, will search the cell for the string of account number.... if it finds it, it will return the character number that it begins in that cell if it doesnt find it, N/A


the IS NUMBER then would react to the character number and return your account number if it is in there or nothing if it is not

DId that do the trick?

Unfortunately no. I finally just highlighted the 5 columns and used Find so I could finish the project. But the link that mtngrrl gave me has some interesting possibilities that I'm going to keep trying.

Try this:

IF(ISNUMBER(FIND(B1,A1),"TRUE","FALSE"))

or

=NOT(ISERROR(SEARCH(A1,B1)))

I think you have to have the cells formatted as "text" for the second one. You might have to switch the A1 and B1 as I can't remember exactly which way the search works off the top of my head.

I had to look it up:

con·cat·e·nate adj (kän-ˈka-tə-nət, kən-)
Definition of CONCATENATE
: linked together