Excel Spreadsheets

ToffeeMark · November 14, 2013

Anyone an expert.

Is there a way to copy the first part of a post code into a new column, or say if column city says Birmingham, post code area = B

Just doing it manually otherwise...

Moomin · November 14, 2013

ToffeeMark said:
Anyone an expert.

Is there a way to copy the first part of a post code into a new column, or say if column city says Birmingham, post code area = B

Just doing it manually otherwise...

Yes, you can create a macro to transfer the data from one column on one spreadsheet to another depending on the rules you set. If you click on macro it should offer help to you

Bigmaclad · November 14, 2013

SAWDUST.

steken1 · November 14, 2013

ToffeeMark said:
Anyone an expert.

Is there a way to copy the first part of a post code into a new column, or say if column city says Birmingham, post code area = B

Just doing it manually otherwise...

If you do highlight for all data, go to the postcode, click the arrow, filter, autofilter, custom and select the option of (i think, mess about with it) begins with B that will show all postcodes beginning with b

Millea · November 14, 2013

Properly detest spreadsheets. Done one today in media, made me so bored I wanted to rip my eyes out but I just ended listening to boss 80's and 90's pop tunes to cheer myself up.

ToffeeMark · November 14, 2013

Found this answer, made sense.

Got to hoover up the sawdust now...

	B	C
JP20035A1	JP
JP20035A2	JP
JK20035A3	JK
JP20035A4	JP
Jz20035A5	Jz
JP20035A6	JP
JP20035A7	JP

[TD="bgcolor: #cacaca, align: center"]21[/TD]

[TD="bgcolor: #cacaca, align: center"]22[/TD]

[TD="bgcolor: #cacaca, align: center"]23[/TD]

[TD="bgcolor: #cacaca, align: center"]24[/TD]

[TD="bgcolor: #cacaca, align: center"]25[/TD]

[TD="bgcolor: #cacaca, align: center"]26[/TD]

[TD="bgcolor: #cacaca, align: center"]27[/TD]

Spreadsheet Formulas

Cell	Formula
C21	=LEFT(B21,2)

Snowball · November 14, 2013

=left(cell with postcode in, MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell with postcode in&{0,1,2,3,4,5,6,7,8,9}))) that should find the position of the first numeric value and then return everything left of that so B15 returns B and ME10 returns ME

peteblue · November 14, 2013

Use one of those self adhesive label makers....

ToffeeMark · November 14, 2013

Snowball said:
=left(cell with postcode in, MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell with postcode in&{0,1,2,3,4,5,6,7,8,9}))) that should find the position of the first numeric value and then return everything left of that so B15 returns B and ME10 returns ME

Thanks for trying to help, that looked really promising, it produced these results.. left is the original cell, right is the result using the formula =LEFT(G2, MIN(FIND({0,1,2,3,4,5,6,7,8,9},G2&{0,1,2,3,4,5,6,7,8,9})))

[TABLE="width: 178"]
[TR]
[TD]AL1 3HD [/TD]
[TD]AL1[/TD]
[/TR]
[TR]
[TD]AL10 0BS[/TD]
[TD]AL1[/TD]
[/TR]
[TR]
[TD]AL2 3JX [/TD]
[TD]AL2[/TD]
[/TR]
[TR]
[TD]AL3 5ER [/TD]
[TD]AL3[/TD]
[/TR]
[TR]
[TD]AL3 5NF [/TD]
[TD]AL3[/TD]
[/TR]
[TR]
[TD]AL7 3UJ [/TD]
[TD]AL7[/TD]
[/TR]
[TR]
[TD]AL7 4HL [/TD]
[TD]AL7[/TD]
[/TR]
[TR]
[TD]AL7 4PL [/TD]
[TD]AL7[/TD]
[/TR]
[TR]
[TD]AL9 7SN [/TD]
[TD]AL9[/TD]
[/TR]
[TR]
[TD]B1 3AL [/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]B10 0UG [/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]B10 9AB [/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]B11 1TX [/TD]
[TD]B1[/TD]
[/TR]
[TR]
[TD]B11 4AN [/TD]
[TD]B1[/TD]
[/TR]
[/TABLE]

Kurt. · November 14, 2013

Turn the monitor on its side mate.

johnnydawg68 · November 15, 2013

Electrical tape always seems to help.

Degs62 · November 15, 2013

Reboot it works for me.

Snowball · November 15, 2013

ToffeeMark said:
Thanks for trying to help, that looked really promising, it produced these results.. left is the original cell, right is the result using the formula =LEFT(G2, MIN(FIND({0,1,2,3,4,5,6,7,8,9},G2&{0,1,2,3,4,5,6,7,8,9})))

[TABLE="width: 178"]
[TR]
[TD]AL1 3HD
[/TD]
[TD]AL1
[/TD]
[/TR]
[TR]
[TD]AL10 0BS
[/TD]
[TD]AL1
[/TD]
[/TR]
[TR]
[TD]AL2 3JX
[/TD]
[TD]AL2
[/TD]
[/TR]
[TR]
[TD]AL3 5ER
[/TD]
[TD]AL3
[/TD]
[/TR]
[TR]
[TD]AL3 5NF
[/TD]
[TD]AL3
[/TD]
[/TR]
[TR]
[TD]AL7 3UJ
[/TD]
[TD]AL7
[/TD]
[/TR]
[TR]
[TD]AL7 4HL
[/TD]
[TD]AL7
[/TD]
[/TR]
[TR]
[TD]AL7 4PL
[/TD]
[TD]AL7
[/TD]
[/TR]
[TR]
[TD]AL9 7SN
[/TD]
[TD]AL9
[/TD]
[/TR]
[TR]
[TD]B1 3AL
[/TD]
[TD]B1
[/TD]
[/TR]
[TR]
[TD]B10 0UG
[/TD]
[TD]B1
[/TD]
[/TR]
[TR]
[TD]B10 9AB
[/TD]
[TD]B1
[/TD]
[/TR]
[TR]
[TD]B11 1TX
[/TD]
[TD]B1
[/TD]
[/TR]
[TR]
[TD]B11 4AN
[/TD]
[TD]B1
[/TD]
[/TR]
[/TABLE]

try =LEFT(G2, MIN(FIND({0,1,2,3,4,5,6,7,8,9},G2&{0,1,2,3,4,5,6,7,8,9}))-1)

Deleted member 28206 · November 15, 2013

Set fire to it.

ToffeeMark · November 15, 2013

Snowball said:
try =LEFT(G2, MIN(FIND({0,1,2,3,4,5,6,7,8,9},G2&{0,1,2,3,4,5,6,7,8,9}))-1)

You are a genius. (y)

Positive rep!!

I am of course going to tell all my colleagues that I am, in fact, A GENIUS!!!

I've basically learned how to handle a larger than 1 gig excel CSV file, (10 Million + rows), bookmark and copy specific terms out into a new spreadsheet in seconds. Merge this spreadsheet which had codes for addresses with full addresses which were contained in a second spreadsheet and now, you have helped me quickly chunk up and group post codes into post code areas.

Madness, no idea I could do that in Excel.

The only thing left to do is allocate a number to specific post code areas. For example. AL, PE, CB, IP, SG = 9

The only downside is that some post codes are split for example DN1-12 & 21 & 22 = 30, but DN18,19, & 31-41 = 28