software

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: www.excelforum.com/member.php...oamp;userid=32192
View this thread: www.excelforum.com/showthread...hreadid=519401Assuming your number to convert is in A1, quot;convertquot; is a NamedRange of your
T? conversions, then put this formula in B1

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

And this one in C1

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

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

Vaya con Dios,
Chuck, CABGx3
quot;phildequot; wrote:

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

quot;phildequot; gt; wrote in
message news
gt;
gt; I have a cell with entry TQ123456. I am trying to find a way of
gt; populating two cells based upon the original entry.
gt;
gt; Firstly split the number, first three in one cell, second in another
gt; cell. Then based upon a lookup, prefix the three numbers in each cell
gt; with a single number.
gt;
gt; Example TQ123456 will become 5123 and 1456 with the prefix of '5' in
gt; one cell and '1' in the other cell a result of a lookup on TQ. If it
gt; had been TF123456 then result would be 5123 and 3456.
gt;
gt; Don't know of this makes sense but it is the conversion of map
gt; references using the British National Grid.
gt;
gt; Can anyone help?
gt;
gt; (If at all possible I would like to also create a second spreadsheet to
gt; 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 amp; 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 0Something similar could be done for the reverse, but I'll leave that to you.

[The data for which squares are which came from
www.gps.gov.uk/natgrid/page9.asp and subsequent pages.]
--
David Biddulph
quot;CLRquot; gt; wrote in message
...
gt; Assuming your number to convert is in A1, quot;convertquot; is a NamedRange of
gt; your
gt; T? conversions, then put this formula in B1
gt;
gt; =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)amp;MID(A1,LEN(A 1)-5,3))*1
gt;
gt; And this one in C1
gt;
gt; =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)amp;RIGHT(A1,3)) *1
gt;
gt; 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
It worked on my test sheet David......I don't know from nothing about
quot;northingsquot; and quot;eastingsquot;....I just used the leading quot;TQquot; and quot;TFquot; the OP
indicated to create my VLOOKUP table.....

Vaya con Dios,
Chuck, CABGx3quot;David Biddulphquot; gt; wrote in message
...
gt; quot;CLRquot; gt; wrote in message
gt; ...
gt; gt; Assuming your number to convert is in A1, quot;convertquot; is a NamedRange of
gt; gt; your
gt; gt; T? conversions, then put this formula in B1
gt; gt;
gt; gt; =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)amp;MID(A1,LEN(A 1)-5,3))*1
gt; gt;
gt; gt; And this one in C1
gt; gt;
gt; gt; =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)amp;RIGHT(A1,3)) *1
gt; gt;
gt; gt; Both can be copied down as far as you have data in Column A
gt;
gt; Are you sure that works?
gt; Doesn't that give the same letter to number conversion for northings as
for
gt; eastings?
gt; --
gt; David Biddulph
gt;
gt;
quot;CLRquot; gt; wrote in message
...
gt; quot;David Biddulphquot; gt; wrote in message
gt; ...
gt;gt; quot;CLRquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Assuming your number to convert is in A1, quot;convertquot; is a NamedRange of
gt;gt; gt; your
gt;gt; gt; T? conversions, then put this formula in B1
gt;gt; gt;
gt;gt; gt; =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)amp;MID(A1,LEN(A 1)-5,3))*1
gt;gt; gt;
gt;gt; gt; And this one in C1
gt;gt; gt;
gt;gt; gt; =(VLOOKUP(LEFT(A1,2),convert,2,FALSE)amp;RIGHT(A1,3)) *1
gt;gt; gt;
gt;gt; gt; Both can be copied down as far as you have data in Column A
gt;gt;
gt;gt; Are you sure that works?
gt;gt; Doesn't that give the same letter to number conversion for northings as
gt; for
gt;gt; eastings?

gt; It worked on my test sheet David......I don't know from nothing about
gt; quot;northingsquot; and quot;eastingsquot;....I just used the leading quot;TQquot; and quot;TFquot; the OP
gt; 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 amp;
northings, and I indexed it separately for 1st amp; 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 (www.gps.gov.uk/natgrid/page13.asp),
and 625 to cover the full grid?
--
David Biddulph
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)amp;MID(A1,LEN(A 1)-5,3))*1
and
=(VLOOKUP(LEFT(A1,2),convert,3,FALSE)amp;RIGHT(A1,3)) *1
....and referring to a quot;3quot; column lookup table instead of a quot;2quot; column one as
I had eluded.

Good job of catching this,... quot;attaboyquot; to you, and my apologies to the OP

Vaya con Dios,
Chuck, CABGx3quot;David Biddulphquot; wrote:

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


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...--
philde
------------------------------------------------------------------------
philde's Profile: www.excelforum.com/member.php...oamp;userid=32192
View this thread: www.excelforum.com/showthread...hreadid=519401quot;phildequot; gt; wrote in
message news
gt;
gt; Thanks for the replies guys. David I tried your solution and it works
gt; fine, it will save a lot of key pressing and of course typos.
gt;
gt; All I need to do is to try and work out the reverse
gt;
gt; 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 (www.gps.gov.uk/natgrid/page12.asp), or the number
of 500km units for the first letter
(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
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