Does anyone know how I can seperate a post code in my data sheet?

G

gsmcellular

Hi,

I have a datasheet in excel and I would like to be able to seperate the
postcode index, the first letter or letters and copy them to another
field in my table.

for example:-

I would like to get SO45 1NQ and have SO in another field

but some postcodes only use one preceeding letter like:-

B1 2NQ and I would only require the letter B here in another field.

Does anyone know how I can easily do this, and then globally do it it
on my datasheet?

Cheers,

Graeme
 
B

Bondi

gsmcellular said:
Hi,

I have a datasheet in excel and I would like to be able to seperate the
postcode index, the first letter or letters and copy them to another
field in my table.

for example:-

I would like to get SO45 1NQ and have SO in another field

but some postcodes only use one preceeding letter like:-

B1 2NQ and I would only require the letter B here in another field.

Does anyone know how I can easily do this, and then globally do it it
on my datasheet?

Cheers,

Graeme
Click to expand...

Hi Graeme,

If you have your postalcode in A1 then if you place this formula in B1
it should extract the letters infront of the first numeric value.

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

Regards,
Bondi
 
G

gsmcellular

Many thanks for that Bondi, do you know of any way I can easil
impliment it down the table.

As in A1,A2,A3 and replicate it in B1,B2,B3 respectivley as I have ove
1000 records.

I am very greatful for any advice!
 
B

Bondi

gsmcellular skrev:
Many thanks for that Bondi, do you know of any way I can easily
impliment it down the table.

As in A1,A2,A3 and replicate it in B1,B2,B3 respectivley as I have over
1000 records.

I am very greatful for any advice!!
Click to expand...

Hi gsmcellular,

I should think that you can just copy down the formula in column B.
Maybe, if you put the formula in B1 you can double click the down/right
corner of the cell (b1) and excel should fill down the column.

Regards,
Bondi
 
Top