software

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!
================================================== ===quot;Reductoquot; gt; 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 quot;XY0987654321quot; I need to produce the result quot;XY05quot;

quot;RagDyeRquot; wrote:

gt; With reference in A1,
gt; Try this in B1:
gt;
gt; =LEFT(A1,4)
gt;
gt; And copy down as needed.
gt; --
gt;
gt; HTH,
gt;
gt; RD
gt; ================================================== ===
gt; Please keep all correspondence within the Group, so all may benefit!
gt; ================================================== ===
gt;
gt;
gt; quot;Reductoquot; gt; wrote in message
gt; ...
gt; In an Excel spreadsheet I have 200,000 (two hundred thousand) Ordnance
gt; Survey
gt; (UK) grid references in the format AB1234567890 (ie a 10-figure reference).
gt; I
gt; need to reduce these to a two-figure reference (ie AB16). Is there a way of
gt; doing this other than manually?
gt;
gt;
gt;

Then try this:

=LEFT(A1,3)amp;MID(A1,8,1)
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===quot;Reductoquot; gt; wrote in message
news
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 quot;XY0987654321quot; I need to produce the result quot;XY05quot;

quot;RagDyeRquot; wrote:

gt; With reference in A1,
gt; Try this in B1:
gt;
gt; =LEFT(A1,4)
gt;
gt; And copy down as needed.
gt; --
gt;
gt; HTH,
gt;
gt; RD
gt; ================================================== ===
gt; Please keep all correspondence within the Group, so all may benefit!
gt; ================================================== ===
gt;
gt;
gt; quot;Reductoquot; gt; wrote in message
gt; ...
gt; In an Excel spreadsheet I have 200,000 (two hundred thousand) Ordnance
gt; Survey
gt; (UK) grid references in the format AB1234567890 (ie a 10-figure
reference).
gt; I
gt; need to reduce these to a two-figure reference (ie AB16). Is there a way
of
gt; doing this other than manually?
gt;
gt;
gt;
When they erect statues for helpful people you will be on the first plinth.
Many thanks.

quot;RagDyeRquot; wrote:

gt; Then try this:
gt;
gt; =LEFT(A1,3)amp;MID(A1,8,1)
gt; --
gt;
gt; HTH,
gt;
gt; RD
gt; ================================================== ===
gt; Please keep all correspondence within the Group, so all may benefit!
gt; ================================================== ===
gt;
gt;
gt; quot;Reductoquot; gt; wrote in message
gt; news
gt; Thanks, and I see where you are coming from, but I specifically need to
gt; produce the two letters followed by the 1st and 6th figure. ie for the
gt; reference quot;XY0987654321quot; I need to produce the result quot;XY05quot;
gt;
gt; quot;RagDyeRquot; wrote:
gt;
gt; gt; With reference in A1,
gt; gt; Try this in B1:
gt; gt;
gt; gt; =LEFT(A1,4)
gt; gt;
gt; gt; And copy down as needed.
gt; gt; --
gt; gt;
gt; gt; HTH,
gt; gt;
gt; gt; RD
gt; gt; ================================================== ===
gt; gt; Please keep all correspondence within the Group, so all may benefit!
gt; gt; ================================================== ===
gt; gt;
gt; gt;
gt; gt; quot;Reductoquot; gt; wrote in message
gt; gt; ...
gt; gt; In an Excel spreadsheet I have 200,000 (two hundred thousand) Ordnance
gt; gt; Survey
gt; gt; (UK) grid references in the format AB1234567890 (ie a 10-figure
gt; reference).
gt; gt; I
gt; gt; need to reduce these to a two-figure reference (ie AB16). Is there a way
gt; of
gt; gt; doing this other than manually?
gt; gt;
gt; gt;
gt; gt;
gt;
gt;
gt;