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


--
gsmcellular
------------------------------------------------------------------------
gsmcellular's Profile: http://www.excelforum.com/member.php...o&userid=36184
View this thread: http://www.excelforum.com/showthread...hreadid=559581

gsmcellular wrote:
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


--
gsmcellular
------------------------------------------------------------------------
gsmcellular's Profile: http://www.excelforum.com/member.php...o&userid=36184
View this thread: http://www.excelforum.com/showthread...hreadid=559581

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

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!!


--
gsmcellular
------------------------------------------------------------------------
gsmcellular's Profile: http://www.excelforum.com/member.php...o&userid=36184
View this thread: http://www.excelforum.com/showthread...hreadid=559581

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!!


--
gsmcellular
------------------------------------------------------------------------
gsmcellular's Profile: http://www.excelforum.com/member.php...o&userid=36184
View this thread: http://www.excelforum.com/showthread...hreadid=559581

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