MS Excel / VBA help thread

You are reading a single comment by @Emyr and its replies. Click here to read the full conversation.

•

pifko
I need to count the number of Postcode Districts that a company covers, but the data I have been provided is displayed in the below format:
```
AL1-10
B1-2,12-15,20-38,40,45-50,65-80,95-98
BB1-12,18
BD1-9,12-24
BH1-7,9-25,31
```
Is there any formula/function I can use to convert to a more excel-friendly format. The ranges (e.g. AL1-10) are causing me problems, otherwise I'd normally do a Text-to-column function and then concatenate the postcode initial with the subsequent numeral...

•

Emyr in reply to @pifko

Can you pipe the input through something which supports LINQ?

static IEnumerable<string> EnumeratePostcodes(IEnumerable<string> postcodeRanges){
    return
        from line in postcodeRanges
        let prefix = new string(line.ToCharArray().TakeWhile (x => Char.IsLetter(x)).ToArray())
        from clause in line.Substring(prefix.Length).Split(',')
        let range = clause.Split('-').Select (c => Convert.ToInt32(c)).ToArray()
        from item in Enumerable.Range(range[0], range.Length == 1 ? 1 : (1+range[1]-range[0]))
        select prefix + item;
}

void Main()
{
    EnumeratePostcodes(new[]{
        "AL1-10",
        "B1-2,12-15,20-38,40,45-50,65-80,95-98",
        "BB1-12,18",
        "BD1-9,12-24",
        "BH1-7,9-25,31"
    }).Dump();
}

IEnumerable (122 items)
AL1
AL2
AL3
AL4
AL5
AL6
AL7
AL8
AL9
AL10
B1
B2
B12
B13
<snip>

MS Excel / VBA help thread

About

LFGSS