|
how do I create an array
I have a column of values that look like: 00000000007310F0000
all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3: G11}),""))) please help |
If alpha characters A through I are the only characters possible within
the text string, try... =LOOKUP(MID(C3,15,1),F3:G11) Otherwise, try... =VLOOKUP(MID(C3,15,1),F3:G11,2,0) Hope this helps! In article , "lost" wrote: I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3: G11}),""))) please help |
I tried the =lookup formula and it returns a single value.
I am looking to convert 00000000007310F0000 to 0000000000731060000 I've tried with =REPLACE(C10,15,1,(IF(MID(C10,15,1)={"A","B","C"," D","E","F"},{"1","2","3","4","5","6"},""))) the above formula only works if the alpha char in the number string = A, otherwise it returns false. ? "Domenic" wrote: If alpha characters A through I are the only characters possible within the text string, try... =LOOKUP(MID(C3,15,1),F3:G11) Otherwise, try... =VLOOKUP(MID(C3,15,1),F3:G11,2,0) Hope this helps! In article , "lost" wrote: I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3: G11}),""))) please help |
Assuming the string is in A2 and that the alpha character is always UPPER
case then =LEFT(A2,14)&CODE(MID(A2,15,1))-64&RIGHT(A2,4) Regards Rowan "lost" wrote: I tried the =lookup formula and it returns a single value. I am looking to convert 00000000007310F0000 to 0000000000731060000 I've tried with =REPLACE(C10,15,1,(IF(MID(C10,15,1)={"A","B","C"," D","E","F"},{"1","2","3","4","5","6"},""))) the above formula only works if the alpha char in the number string = A, otherwise it returns false. ? "Domenic" wrote: If alpha characters A through I are the only characters possible within the text string, try... =LOOKUP(MID(C3,15,1),F3:G11) Otherwise, try... =VLOOKUP(MID(C3,15,1),F3:G11,2,0) Hope this helps! In article , "lost" wrote: I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3: G11}),""))) please help |
well, I have not idea why it worked, but it did, kinda.
the range of non-numeric values is as follows: indicating positive values (0-9) { A B C D E F G H I indicating negative values (0-9) } J K L M N O P Q R your solution worked when alpha values = A - I. ?? thanks! "Rowan" wrote: Assuming the string is in A2 and that the alpha character is always UPPER case then =LEFT(A2,14)&CODE(MID(A2,15,1))-64&RIGHT(A2,4) Regards Rowan "lost" wrote: I tried the =lookup formula and it returns a single value. I am looking to convert 00000000007310F0000 to 0000000000731060000 I've tried with =REPLACE(C10,15,1,(IF(MID(C10,15,1)={"A","B","C"," D","E","F"},{"1","2","3","4","5","6"},""))) the above formula only works if the alpha char in the number string = A, otherwise it returns false. ? "Domenic" wrote: If alpha characters A through I are the only characters possible within the text string, try... =LOOKUP(MID(C3,15,1),F3:G11) Otherwise, try... =VLOOKUP(MID(C3,15,1),F3:G11,2,0) Hope this helps! In article , "lost" wrote: I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3: G11}),""))) please help |
I have no idea why that worked, but it did, kinda....
range values are as follows: positive values (0-9) { A B C D E F G H I negative values (0-9) } J K L M N O P Q R your solution worked when the alpha number was A - I, otherwise not. ?? thanks!! "Rowan" wrote: Assuming the string is in A2 and that the alpha character is always UPPER case then =LEFT(A2,14)&CODE(MID(A2,15,1))-64&RIGHT(A2,4) Regards Rowan "lost" wrote: I tried the =lookup formula and it returns a single value. I am looking to convert 00000000007310F0000 to 0000000000731060000 I've tried with =REPLACE(C10,15,1,(IF(MID(C10,15,1)={"A","B","C"," D","E","F"},{"1","2","3","4","5","6"},""))) the above formula only works if the alpha char in the number string = A, otherwise it returns false. ? "Domenic" wrote: If alpha characters A through I are the only characters possible within the text string, try... =LOOKUP(MID(C3,15,1),F3:G11) Otherwise, try... =VLOOKUP(MID(C3,15,1),F3:G11,2,0) Hope this helps! In article , "lost" wrote: I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3: G11}),""))) please help |
The solution I posted works by subtracting a value from the alpha character's
ASCII code. In the ASCII character set A is 65, B 66 etc. The ranges you have just posted do not quite tie up: positive values (0-9) { A B C D E F G H I negative values (0-9) } J K L M N O P Q R You now have ten values for nine letters so I am going to assume you meant positive values (1-9) { A B C D E F G H I negative values (1-9) } J K L M N O P Q R in which case 00000000007310J0000 gets converted to 00000000007310-10000 and 00000000007310F0000 gets converted to 0000000000731060000 If this is what you are after then the forumla is =LEFT(A2,14)&IF(CODE(MID(A2,15,1))<74,CODE(MID(A2, 15,1))-64,CODE(MID(A2,15,1))-75)&RIGHT(A2,4) with no validation to check that the alpha character is in the required ranges. If this isn't what you want then post more examples of how the conversions should work. Regards Rowan "lost" wrote: I have no idea why that worked, but it did, kinda.... range values are as follows: positive values (0-9) { A B C D E F G H I negative values (0-9) } J K L M N O P Q R your solution worked when the alpha number was A - I, otherwise not. ?? thanks!! "Rowan" wrote: Assuming the string is in A2 and that the alpha character is always UPPER case then =LEFT(A2,14)&CODE(MID(A2,15,1))-64&RIGHT(A2,4) Regards Rowan "lost" wrote: I tried the =lookup formula and it returns a single value. I am looking to convert 00000000007310F0000 to 0000000000731060000 I've tried with =REPLACE(C10,15,1,(IF(MID(C10,15,1)={"A","B","C"," D","E","F"},{"1","2","3","4","5","6"},""))) the above formula only works if the alpha char in the number string = A, otherwise it returns false. ? "Domenic" wrote: If alpha characters A through I are the only characters possible within the text string, try... =LOOKUP(MID(C3,15,1),F3:G11) Otherwise, try... =VLOOKUP(MID(C3,15,1),F3:G11,2,0) Hope this helps! In article , "lost" wrote: I have a column of values that look like: 00000000007310F0000 all cells are 19 chars in length, and the 15th char is alpha. I need to map the 15th alpha char to a numeric. the mapping is as follows: ABCDEFGHI (column F) 123456789 (column G) this is where I'm at: =REPLACE(C3,15,1,(IF(MID(C3,15,1)=({F3:F12}),({G3: G11}),""))) please help |
| All times are GMT +1. The time now is 07:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com