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
|