Rationalising grid references
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? |
Rationalising grid references
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? |
Rationalising grid references
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? |
Rationalising grid references
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? |
Rationalising grid references
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? |
All times are GMT +1. The time now is 04:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com