MS Excel / VBA help thread

You could use Left and Right (with some Ifs to determine the correct splits) to separate out the two sides of the range, then it’s a fairly simple task of addition and subtraction.

How many companies is this for?

If it's a one off, you could try this:

text to columns
use find("-",a1) and mid / left / right to get the numbers either side of the dashes
use substitute(a1,substitute(A1,"1")) to remove the letters. or just type "1" in each cell...
concatenate stuff

I think I would get rid of the letters entirely. Be very careful with London postcodes like EC1A ... , you might have to do this step manually for those.

Then do a pattern search for any single digits (none in your example) and replace with a temporary symbol such as 'x'. Do the same for any double digits like 40, 18, 31 in your example. You'll count these as '1' later.

Then split to columns on ','. The ranges should collapse to an negative integer one less than the count of postcodes. So '1-10' will now be '9'.

Add the count of numeric cells to the sum of numeric cells. Add the count of 'x' to this and that should be your answer.

*Edit: Hmm, I think I must have dreamt that Excel had pattern/regex matching in Search and Replace... it can be done in Word but it's a bit fiddly.

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>