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
|