if all the prefixes are the same length, you can use the Left function to

Mar 4, 2021
10:31:01am

Cougarclaw Intervention Needed

separate them in a new column. Then you can create a vlookup to see if any of those 5 digit codes match the prefixes you're looking for, and have them pull a second column from the prefix table with a "Y" flag. Then you can filter on the flags.

Report problem with this ad

Cougarclaw

Previous username: BigWoodThunder
Bio page: Cougarclaw
Joined: Jun 22, 2011
Last login: May 5, 2024
Total posts: 59,627 (14,939 FO)

Report problem with this ad

Messages

Author

Time

Excel question: I've got a series of prefixes and I want to highlight

David Lo Pan

3/4/21 10:28am

Do you need to highlight it? or just flag it somehow?

LeonMarek

3/4/21 10:30am

Would be open to flagging it in an alternative manner

David Lo Pan

3/4/21 10:33am

if all the prefixes are the same length, you can use the Left function to

Cougarclaw

3/4/21 10:31am

This might work. There are some prefixes that are different lengths but I

David Lo Pan

3/4/21 10:42am

Any similar items after the prefixes? Such as a dash or space?

Bin01015

3/4/21 10:46am

Sometimes a dash, sometimes one letter, sometimes another letter, etc.

David Lo Pan

3/4/21 10:50am

How many prefixes in total?

Bin01015

3/4/21 11:04am

120 ish (and growing over time)

David Lo Pan

3/4/21 11:20am

RE: 120 ish (and growing over time)

Bin01015

3/4/21 11:37am

BTW the — is two dashes without a space.

Bin01015

3/4/21 3:04pm

This put me on the right track. I ended up using the following conditional

David Lo Pan

3/4/21 10:03pm

If you have a table of all your prefixes, you can do a vlookup in a separate column for your "column to highlight"

Indy Coug

3/4/21 10:31am

What result would I be putting in the "vlookup in a separate column"?

David Lo Pan

3/4/21 10:45am

Sorry, I take it back. I think you'd need a VBA solution where you loop each cell through the table of prefixes

Indy Coug

3/4/21 10:50am

No worries. I'm so slow at VBA, but should probably take the time to get better

David Lo Pan

3/4/21 10:53am

There is a newer xlookup function that gives you additional capabilities on ranges to return and matching etc. Worth a

Posting on CougarBoard