@Eloise Are you interested in
1. A manual process involving 'Remove Duplicates' and 'Sort'
2. A formula-based approach (distinctly cumbersome at present)
3. A future dynamic array approach
4. A Power Query solution
5. A macro automating 1 or 4
6. A purpose-written code solution
Fantastic!Or,
Try this shorter formula in replaced of the 15 IFs formula in column F and the helper in column E
In G5, copied down :
=IF(LEN(C5)<51,IFERROR(0+MID(C5,MIN(FIND({0,1,2,3,4}+{0;5},C5&1/19,ISERR(-MID(C5,MIN(FIND({0,1,2,3,4}+{0;5},C5&1/19))+1,1))*MIN(FIND({1,2,3,4,5,6,7,8,9,0},C5&1/19))+1)),2),0),0)
Regards
Bosco
I like your approach. Seems simple yet does the job!Am I being dim. What is wrong with using the same approach as your conditional formatting?
By the way, the standard way of looking up substrings that match any specified pattern within a search string is to use Regular Expressions. This can be called from Excel using UDF to access functions within the Microsoft Scripting Library.
Thank you. I will put it in my rainy day file.Just for the record. This version is one you might wish to save in a remote folder somewhere as insurance against a rainy day. It uses a RegEx function written by Patrick G. Matthews and it is called as a user-defined function. The particular pattern I have set simply returns the first pair of digits it finds within the string representing the model. The approach is capable of searches against far more subtle combinations of requirements but it is slow (approx. 1 min to update) applied to a list as large as yours.
Peter
p.s. The alternative pattern LC-?(\d{2}) would restrict the search to a pair of digits that are preceded by LC or LC- (the ? makes the - optional).