partially match prefix to full number

[excelnow]

I have a list of prefixes of security identifiers (column a), they range from 4 numbers to 5numbers maybe with one letter in between(within that cell). Then i have another list with the full identifier(column b), I want to compare these two lists to find full identifers with the same prefix in (column a).

The list won't be the same in number, that is (column a) may go right down to A100 while (column b) may only go to b50.

And most likely they won't be sorted.


So the prefixes will be my main list, i would save in the spreadsheet alone and the numbers that would go into column b i'm importing from another spreadsheet. So I need a formula in my main spreadsheet with the prefixes to say, the full numbers i'm going to import into column b match them against all the prefixes in column a, if there's a match turn the cell in column b blue for instance.

i hope this is clear

[teylyn]

A spreadsheet with some mocked up data will say more than a thousand words.

Can you attach a file and explain in context?

cheers,

[DonkeyOte]

If you're looking to format B (complete) based on whether or not a partial match can be found in A then you could use a Conditional Format rule applied to B1:B50 along the lines of:

Please Login or Register  to view this content.

[excelnow]

I've attached a small example of what i'm looking for.

[excelnow]

sorry, in my example the prefixes are in column b.

[DonkeyOte]

With the revised layout - and given values are numeric - one conditional rule you could apply to A1:A11 would be:

Please Login or Register  to view this content.

[excelnow]

It works, thanks a million