Rationalising grid references

[Guest]

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?

 

[RagDyeR]

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


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?

 

[Guest]

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]

Then try this:

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

HTH,

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


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"

 

[Guest]

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