Hello there
I have a list of shortened Product numbers in my table per cell.
The content of a cell looks like this:
Example one
1234567/8/9/6345/6 (01)
What it means is: “there are unique Product numbers 1234567, 1234568, 1234569, 1236345, 1236346 and they belong to group 01”
Example two
1234567/2434567 (12)
What it means is: “there are unique Product numbers 1234567, 2434567 and they belong to group 12”
Logic is as follows:
- if the preceding number contains all the values from the start, insert slash and display only deviating numbers afterwards. Repeat from next predecessor left. Print any number after the slash that is not contained (in order) in predecessor.
- Product Number length is always 7 digits
- there will be between one and 6 unique numbers displayed (example above shows 5, to be clear what I mean). Never less, never more.
- Group will always be 2 digits in brackets
What I would like to achieve:
- add 6 Columns: “1st Number”, “2nd” Number" etc… each displaying the full number to be extracted. (See: “what it means” in examples above). If there is one.
- discard Group
What I have thought of but don’t know how to express it by formula:
- Remove three digits from the right (discard Group and leading space)
- Count length of digits after the slash, substract length from 7 and insert resulting amount from value of predecessor (this is where I struggle, since it is not always x from the beginning of the cell)
Thank you very much in advance
