I have a query I'm trying to solve but not sure where to start with this one. If I have a range, where certain cells use a hyphen to represent a sequence, I need to be able to replace the hyphen with the sequence itself, separated by spaces
Example data:
A B C D E F
Row 1 2 5 6 7 8-14
Row 2 2 4 6 8 10
Row 3 2 4 6 8-11
Row 4 2 4 6 8-12
Row 5 2 4 6 8 13-19
Row 6 2 4 6 8 13-19
Row 7 2 4 6 8 13-19
Row 8 2-5 7 8 13-15 22-25
Target:
A B C D E F
Row 1 2 5 6 7 8 9 10 11 12 13 14
Row 2 2 4 6 8 10
Row 3 2 4 6 8 9 10 11
Row 4 2 4 6 8 9 10 11 12
Row 5 2 4 6 8 13 14 15 16 17 18 19
Row 6 2 4 6 8 13 14 15 16 17 18 19
Row 7 2 4 6 8 13 14 15 16 17 18 19
Row 8 2 3 4 5 7 8 13 14 15 22 23 24 25
Background:
This is to tidy up a bill of materials for work, where the formatting of legacy products is terrible. Rather than using a standard format (which is now in place... D3 D4 D5 D8 D9 D10), the files were written as D3-5/8-D10 (or multiple combinations of hyphens, slashes and commas. I've already used a formula to separate the alpha characters and park them in an unaffected range, while using VBA to delimit the text into separate columns, and regex to remove the inconsistent alpha characters. The plan is to remove all variables, and then combine them into a single string.
Bookmarks