Lookup?

[philde]

I have a cell with entry TQ123456. I am trying to find a way of populating two cells based upon the original entry.

Firstly split the number, first three in one cell, second in another cell. Then based upon a lookup, prefix the three numbers in each cell with a single number.

Example TQ123456 will become 5123 and 1456 with the prefix of '5' in one cell and '1' in the other cell a result of a lookup on TQ. If it had been TF123456 then result would be 5123 and 3456.

Don't know of this makes sense but it is the conversion of map references using the British National Grid.

Can anyone help?

(If at all possible I would like to also create a second spreadsheet to give the reverse, but maybe I'm expecting too much! )

Thanks anyway.

Regards Phil...

[CLR]

Assuming your number to convert is in A1, "convert" is a NamedRange of your
T? conversions, then put this formula in B1

=(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&MID(A1,LEN(A1)-5,3))*1

And this one in C1

=(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&RIGHT(A1,3))*1

Both can be copied down as far as you have data in Column A

Vaya con Dios,
Chuck, CABGx3



"philde" wrote:

>
> I have a cell with entry TQ123456. I am trying to find a way of
> populating two cells based upon the original entry.
>
> Firstly split the number, first three in one cell, second in another
> cell. Then based upon a lookup, prefix the three numbers in each cell
> with a single number.
>
> Example TQ123456 will become 5123 and 1456 with the prefix of '5' in
> one cell and '1' in the other cell a result of a lookup on TQ. If it
> had been TF123456 then result would be 5123 and 3456.
>
> Don't know of this makes sense but it is the conversion of map
> references using the British National Grid.
>
> Can anyone help?
>
> (If at all possible I would like to also create a second spreadsheet to
> give the reverse, but maybe I'm expecting too much! )
>
> Thanks anyway.
>
> Regards Phil...
>
>
> --
> philde
> ------------------------------------------------------------------------
> philde's Profile: http://www.excelforum.com/member.php...o&userid=32192
> View this thread: http://www.excelforum.com/showthread...hreadid=519401
>
>

[David Biddulph]

"philde" <[email protected]> wrote in
message news:[email protected]...
>
> I have a cell with entry TQ123456. I am trying to find a way of
> populating two cells based upon the original entry.
>
> Firstly split the number, first three in one cell, second in another
> cell. Then based upon a lookup, prefix the three numbers in each cell
> with a single number.
>
> Example TQ123456 will become 5123 and 1456 with the prefix of '5' in
> one cell and '1' in the other cell a result of a lookup on TQ. If it
> had been TF123456 then result would be 5123 and 3456.
>
> Don't know of this makes sense but it is the conversion of map
> references using the British National Grid.
>
> Can anyone help?
>
> (If at all possible I would like to also create a second spreadsheet to
> give the reverse, but maybe I'm expecting too much! )

If your input string is in cell A4, I've used the eastings formula:
=1000*(VLOOKUP(LEFT(A4,1),Sheet2!A2:E27,2)+VLOOKUP(MID(A4,2,1),Sheet2!A2:E27,4))+MID(A4,3,3)
and the northings formula:
=1000*(VLOOKUP(LEFT(A4,1),Sheet2!A2:E27,3)+VLOOKUP(MID(A4,2,1),Sheet2!A2:E27,5))+MID(A4,6,3)

The VLOOKUP is going to a second sheet where I've put the co-ordinates for
the corner for the first & second letters, as follows:
First letter Second letter
Easting Northings Eastings Northings
A -10 15 0 4
B -5 15 1 4
C 0 15 2 4
D 5 15 3 4
E 10 15 4 4
F -10 10 0 3
G -5 10 1 3
H 0 10 2 3
J 5 10 3 3
K 10 10 4 3
L -10 5 0 2
M -5 5 1 2
N 0 5 2 2
O 5 5 3 2
P 10 5 4 2
Q -10 0 0 1
R -5 0 1 1
S 0 0 2 1
T 5 0 3 1
U 10 0 4 1
V -10 -5 0 0
W -5 -5 1 0
X 0 -5 2 0
Y 5 -5 3 0
Z 10 -5 4 0


Something similar could be done for the reverse, but I'll leave that to you.

[The data for which squares are which came from
http://www.gps.gov.uk/natgrid/page9.asp and subsequent pages.]
--
David Biddulph

[David Biddulph]

"CLR" <[email protected]> wrote in message
news:[email protected]...
> Assuming your number to convert is in A1, "convert" is a NamedRange of
> your
> T? conversions, then put this formula in B1
>
> =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&MID(A1,LEN(A1)-5,3))*1
>
> And this one in C1
>
> =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&RIGHT(A1,3))*1
>
> Both can be copied down as far as you have data in Column A

Are you sure that works?
Doesn't that give the same letter to number conversion for northings as for
eastings?
--
David Biddulph

[CLR]

It worked on my test sheet David......I don't know from nothing about
"northings" and "eastings"....I just used the leading "TQ" and "TF" the OP
indicated to create my VLOOKUP table.....

Vaya con Dios,
Chuck, CABGx3


"David Biddulph" <[email protected]> wrote in message
news:[email protected]...
> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > Assuming your number to convert is in A1, "convert" is a NamedRange of
> > your
> > T? conversions, then put this formula in B1
> >
> > =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&MID(A1,LEN(A1)-5,3))*1
> >
> > And this one in C1
> >
> > =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&RIGHT(A1,3))*1
> >
> > Both can be copied down as far as you have data in Column A
>
> Are you sure that works?
> Doesn't that give the same letter to number conversion for northings as
for
> eastings?
> --
> David Biddulph
>
>

[David Biddulph]

"CLR" <[email protected]> wrote in message
news:[email protected]...
> "David Biddulph" <[email protected]> wrote in message
> news:[email protected]...
>> "CLR" <[email protected]> wrote in message
>> news:[email protected]...
>> > Assuming your number to convert is in A1, "convert" is a NamedRange of
>> > your
>> > T? conversions, then put this formula in B1
>> >
>> > =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&MID(A1,LEN(A1)-5,3))*1
>> >
>> > And this one in C1
>> >
>> > =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&RIGHT(A1,3))*1
>> >
>> > Both can be copied down as far as you have data in Column A
>>
>> Are you sure that works?
>> Doesn't that give the same letter to number conversion for northings as
> for
>> eastings?

> It worked on my test sheet David......I don't know from nothing about
> "northings" and "eastings"....I just used the leading "TQ" and "TF" the OP
> indicated to create my VLOOKUP table.....

I'm trying to imagine what your VLOOKUP table looks like. In the TQ case
you seem to have used the function
=(VLOOKUP(LEFT(A1,2),convert,2,FALSE)
to give the prefix 5 for the 123 (your MID) string and then the same
function to give the prefix 1 for the 456 (your RIGHT) string?
Similarly for TF your VLOOKUP function seems to give 5 one time and then 3
the next? Am I being dim?

That's why in my solution I used a separate output column for eastings &
northings, and I indexed it separately for 1st & 2nd letters to keep my
VLOOKUP table of manageable length. For your table I imagine you need 55
entries for the land areas of GB (http://www.gps.gov.uk/natgrid/page13.asp),
and 625 to cover the full grid?
--
David Biddulph

[CLR]

Ok, David, after checking everything, I see that I mis-read the post. You
are indeed correct. My formulas should have read
=(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&MID(A1,LEN(A1)-5,3))*1
and
=(VLOOKUP(LEFT(A1,2),convert,3,FALSE)&RIGHT(A1,3))*1
....and referring to a "3" column lookup table instead of a "2" column one as
I had eluded.

Good job of catching this,... "attaboy" to you, and my apologies to the OP

Vaya con Dios,
Chuck, CABGx3





"David Biddulph" wrote:

> "CLR" <[email protected]> wrote in message
> news:[email protected]...
> > "David Biddulph" <[email protected]> wrote in message
> > news:[email protected]...
> >> "CLR" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Assuming your number to convert is in A1, "convert" is a NamedRange of
> >> > your
> >> > T? conversions, then put this formula in B1
> >> >
> >> > =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&MID(A1,LEN(A1)-5,3))*1
> >> >
> >> > And this one in C1
> >> >
> >> > =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)&RIGHT(A1,3))*1
> >> >
> >> > Both can be copied down as far as you have data in Column A
> >>
> >> Are you sure that works?
> >> Doesn't that give the same letter to number conversion for northings as
> > for
> >> eastings?
>
> > It worked on my test sheet David......I don't know from nothing about
> > "northings" and "eastings"....I just used the leading "TQ" and "TF" the OP
> > indicated to create my VLOOKUP table.....
>
> I'm trying to imagine what your VLOOKUP table looks like. In the TQ case
> you seem to have used the function
> =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)
> to give the prefix 5 for the 123 (your MID) string and then the same
> function to give the prefix 1 for the 456 (your RIGHT) string?
> Similarly for TF your VLOOKUP function seems to give 5 one time and then 3
> the next? Am I being dim?
>
> That's why in my solution I used a separate output column for eastings &
> northings, and I indexed it separately for 1st & 2nd letters to keep my
> VLOOKUP table of manageable length. For your table I imagine you need 55
> entries for the land areas of GB (http://www.gps.gov.uk/natgrid/page13.asp),
> and 625 to cover the full grid?
> --
> David Biddulph
>
>
>

[philde]

Thanks for the replies guys. David I tried your solution and it works fine, it will save a lot of key pressing and of course typos.

All I need to do is to try and work out the reverse

Regards Phil...

[David Biddulph]

"philde" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks for the replies guys. David I tried your solution and it works
> fine, it will save a lot of key pressing and of course typos.
>
> All I need to do is to try and work out the reverse
>
> Regards Phil...

For the reverse I would have thought that the easy option was just to index
the letters by going into the 5 by 5 matrix.

If you put the letters in a square (in cells A1 to E5):
A B C D E
F G H J K
L M N O P
Q R S T U
V W X Y Z

then you can get the requisite letter by
INDEX(A1:E5,5-northings,eastings+1),
where the eastings and northings are the number of 100km units (0 to 4) for
the second letter (http://www.gps.gov.uk/natgrid/page12.asp), or the number
of 500km units for the first letter
(http://www.gps.gov.uk/natgrid/page9.asp) [but bear in mind that you'll need
to allow an offset for the false origin in the latter case].

I'll pop an example temporarily at
http://www.rowing.biddulph.btinterne...tionalGrid.xls.
[The reverse calculation is towards the right-hand side of the page, and
comfortingly gets back to the reference we started from.]
--
David Biddulph