how do I create an array

L

lost

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
 
D

Domenic

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!
 
L

lost

I tried the =lookup formula and it returns a single value.

I am looking to convert 00000000007310F0000 to 0000000000731060000

I've tried wit
=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.

?
 
R

Rowan

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
 
L

lost

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!
 
L

lost

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!!
 
R

Rowan

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
Click to expand...

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
Click to expand...

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
 
Top