Generate every value between two values (letters and numbers)

I have a list of Canadian postal code ranges (A0A1A0-A0A4Z9, A0B1A0-A0B3Z9, etc.). I need to generate every postal code within each range to create a full list

The postal codes are sequential. A0A1A0 would be followed by A0A1A1, A0A1A2, A0A1A3, etc. After A0A1A9, the next code would be A0A1B0.

The attached file is the exact one I'm working from. Is there a simple formula or VBA based way to do this?

You realize that's close to 250,000 postal codes? What would you do with them?

Yes, I realize it'll be a lot of postal codes.

I have a list of about 241,000 shipments. I need to determine whether the ship from / ship to postal code on any of those shipments is contained in one of those ranges. The list of postal codes determine whether a carrier (FedEx in this case) will assign an out of area fee to the shipment.

**** Revise formula, should be correct *****

This is a combination of Formula and Power Query.

B2:C2
=(CODE(MID(MID($A2,COLUMNS($B2:B2)*7-3,3),2,1))-65)*10+SUM(MID(MID($A2,COLUMNS($B2:B2)*7-3,3),{1,3},1)*{260,1})

Data > From Table > Advance editor > Paste code below

Please Login or Register  to view this content.

Load table to E1

G2
=E2&INT(F2/260)&CHAR(MOD(INT(F2/10),26)+65)&RIGHT(F2)


Right click at E2 and Refresh to get full 231k list

You don't need to list all the codes to do that. Split col A into two columns using Text to columns, and you can use a simple, fast formula:

	A	B	C	D	E	F
1	From	To		Code	Group
2	A0A1A0	A0A4Z9		A0A1A0	A0A1A0-A0A4Z9	E2: =IFERROR(IF(VLOOKUP(D2, $A$2:$B$329, 2) >= D2, LOOKUP(D2, A2:A329) & "-" & VLOOKUP(D2, $A$2:$B$329, 2), "---"), "---")
3	A0B1A0	A0B3Z9		U1G3Y0	---
4	A0C1A0	A0C2Z9		G8K0R1	G8K0A0-G8K3Z9
5	A0E1A0	A0E3Z9		I3U6Q5	---
6	A0G1A0	A0G4Z9		V0N0L1	V0N0A0-V0N2Z9
7	A0H1A0	A0H2Z9		P9A3G2	P9A0A0-P9A4Z9
8	A0J1A0	A0J1Z9
9	A0K1A0	A0K5Z9
10	A0L1A0	A0L1Z9
11	A0M1A0	A0M1Z9
12	A0N1A0	A0N2Z9
13	A0P1A0	A0P1Z9
14	A0R1A0	A0R1Z9
15	A1S0A0	A1S1Z9

You have some invalid postal codes:

	A	B
1	From	To
269	T1O1A0	T1O1Z9
325	X1O1A0	X1O1Z9
329	Y1O1A0	Y1O1Z9

I think the valid codes expand to 177,200.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #1 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread