Per my prior post I would be inclined to go the UDF route, but it might be that your source data is sufficiently limited that the approach offered in prior post will suffice?
Below would be one possible UDF, this should work irrespective of how your data is sorted, and/or how many variants you need to account for in a given material no.
Caveat: the below does assume that when swapping out 1+ strings within same element {e.g. elements 3 & 4} that the before/after values are like-for-like in terms of their respective length - e.g. 20&30 rather than 30&100
the above would be stored in a Module in VBE, and could then be called from your cells along lines of:
note: I inserted a blank row 1 just to limit how many times you call the UDF -- i.e. it will only fire the first time it finds an item number, else it will use whatever result it conjured previously.
Bookmarks