In an Excel spreadsheet I have 200,000 (two hundred thousand) Ordnance Survey
(UK) grid references in the format AB1234567890 (ie a 10-figure reference). I
need to reduce these to a two-figure reference (ie AB16). Is there a way of
doing this other than manually?

With reference in A1,
Try this in B1:

=LEFT(A1,4)

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Reducto" wrote in message
...
In an Excel spreadsheet I have 200,000 (two hundred thousand) Ordnance
Survey
(UK) grid references in the format AB1234567890 (ie a 10-figure reference).
I
need to reduce these to a two-figure reference (ie AB16). Is there a way of
doing this other than manually?

Thanks, and I see where you are coming from, but I specifically need to
produce the two letters followed by the 1st and 6th figure. ie for the
reference "XY0987654321" I need to produce the result "XY05"

"RagDyeR" wrote:

With reference in A1,
Try this in B1:

=LEFT(A1,4)

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Reducto" wrote in message
...
In an Excel spreadsheet I have 200,000 (two hundred thousand) Ordnance
Survey
(UK) grid references in the format AB1234567890 (ie a 10-figure reference).
I
need to reduce these to a two-figure reference (ie AB16). Is there a way of
doing this other than manually?

Then try this:

=LEFT(A1,3)&MID(A1,8,1)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Reducto" wrote in message
...
Thanks, and I see where you are coming from, but I specifically need to
produce the two letters followed by the 1st and 6th figure. ie for the
reference "XY0987654321" I need to produce the result "XY05"

"RagDyeR" wrote:

With reference in A1,
Try this in B1:

=LEFT(A1,4)

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Reducto" wrote in message
...
In an Excel spreadsheet I have 200,000 (two hundred thousand) Ordnance
Survey
(UK) grid references in the format AB1234567890 (ie a 10-figure
reference).
I
need to reduce these to a two-figure reference (ie AB16). Is there a way
of
doing this other than manually?

When they erect statues for helpful people you will be on the first plinth.
Many thanks.

"RagDyeR" wrote:

Then try this:

=LEFT(A1,3)&MID(A1,8,1)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Reducto" wrote in message
...
Thanks, and I see where you are coming from, but I specifically need to
produce the two letters followed by the 1st and 6th figure. ie for the
reference "XY0987654321" I need to produce the result "XY05"

"RagDyeR" wrote:

With reference in A1,
Try this in B1:

=LEFT(A1,4)

And copy down as needed.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Reducto" wrote in message
...
In an Excel spreadsheet I have 200,000 (two hundred thousand) Ordnance
Survey
(UK) grid references in the format AB1234567890 (ie a 10-figure
reference).
I
need to reduce these to a two-figure reference (ie AB16). Is there a way
of
doing this other than manually?