create random postcodes for testing

[icecurtain]

Can anyone help with a formula for creating random UK postcodes.

it is only for testing

so ??## #?? and ?## #??

would be fine.

 

[Richard Schollar]

I expect there's slicker ways, but this will work:

<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">RB27 9DV</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">MA99 3LY</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">IV95 4KE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Z80 6TK</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Z88 4TK</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">G86 8VB</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">I44 7DS</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">K61 2BC</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">X56 8YP</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Z78 7HY</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">H13 9FI</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">LF34 2RU</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">VH63 5RW</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">H76 8RA</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">I68 1LV</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">NS64 1NH</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">T69 4MP</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">CU74 4VH</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">RN60 2NY</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">JW88 9MO</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">H24 6AI</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">I97 2VV</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">T11 6AH</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">CF42 9RY</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=CHAR(<font color="Blue">RANDBETWEEN(<font color="Red">65,90</font>)</font>)&IF(<font color="Blue">RAND(<font color="Red"></font>)>0.5,"",CHAR(<font color="Red">RANDBETWEEN(<font color="Green">65,90</font>)</font>)</font>)&RANDBETWEEN(<font color="Blue">10,99</font>)&" "&RANDBETWEEN(<font color="Blue">1,9</font>)&CHAR(<font color="Blue">RANDBETWEEN(<font color="Red">65,90</font>)</font>)&CHAR(<font color="Blue">RANDBETWEEN(<font color="Red">65,90</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Need ATP installed in xl2003 and below.

 

[GlennUK]

Would they have to be valid UK postcodes?

And what about :
??#? #?? such as EC3M 4AD

 

[icecurtain]

Spot on Richard,

Thanks

 

[icecurtain]

Would they have to be valid UK postcodes?

And what about :
??#? #?? such as EC3M 4AD

Maybe later on in testing but the formula would be extreme

http://www.mrexcel.com/forum/showthread.php?t=436118&highlight=validate+postcode

 

[cornflakegirl]

It's not going to generate you any postcodes of the form XX# #XX eg CB1 1AB. Which will probably exclude a significant proportion of addresses (every postcode I've had has been in this form).

(I realise this is what you specified originally - just wondered if it was what you actually required though.)

 

[GlennUK]

Maybe later on in testing but the formula would be extreme

http://www.mrexcel.com/forum/showthread.php?t=436118&highlight=validate+postcode

Can you say why you don't want to use the function given in that thread?

 

[icecurtain]

The testing is for DW I just need lots of random data in the right format to start with then, I will move onto valid Postcodes. But I think the formula would be to complex even for the most hardy excel guru and maybe VB might be then way ahead.

 

[icecurtain]

This function is for validation not creation.

 

[GlennUK]

This function is for validation not creation.

I was under the impression that you want to create postcodes to test some postcode validation process ... one that you are trying to do with formulas.

I was asking why you are using formulas to do validation instead of the function. Sorry for confusing you.