Pulling a Letter from a cell and filling another cell with info

[nick s]

I am a newbee at this so bare with me......

Here is what I am trying to do with no luck so far.
If I type RS23U1R109000 in a cell A1, I want B1 to read the 5th letter or
number and fill B1 with E86.

Example
A1= RS23U1R109000 B1=E86
A1= RS23V1R109000 B1=E87
A1= RS23R1R109000 B1=E84

As you can see in my example, the 5th letter could be U,V,R or whatever, but
I need cell B1 to read that letter and populate B1 with E86, E87, E84 or
whatever.

I hope I haven't confused anyone...

 

[Domenic]

Let A1:B3 contain the following table...

U E86
V E87
R E84

Then, use the following formula...

=VLOOKUP(MID(C1,5,1),$A$1:$B$3,2,0)

....where C1 contains a value such as RS23U1R109000. Adjust the table
accordingly and formula accordingly.

Hope this helps!

 

[blackburnjl]

I'm not sure that I understand your question, but if I read it
correctly, you want to do a lookup on the 5th character in a alpha/text
number from a data list that is in E84:E86, (assuming that there are
data labels in D8486.

If this is the case, try using the following formula in B1:

VLOOKUP(MID(A1,5,1),D84:E86,2)

Hope this helps.

JimB

 

[Ron Coderre]

Not sure exactly where you are getting the associated number to append to the
"E", so maybe this will get you in the right direction:

This formula will return the ASCII code for the uppercase, 5th letter in
cell A1:
=CODE(UPPER(MID(A1,5,1)))

Here's what it return for various letters:
A....65
B....66
R....82
S....83
T....84
U....85
V....86

So if you meant the ASCII code, then this would work:
="E"&CODE(UPPER(MID(A1,5,1)))

BUT...
If you have some non-sequential numbers to assign to the letter, you might
try one of these:

This one assign whatever you want to the ASCII code:
="E"&CHOOSE(CODE(UPPER(MID(A1,5,1)))-64,100,105,18,23,12,109...etc to 26
value)
It subtracts 64 from the code, so A:1, B:2.....Z:26
Then, for each value from 1 to 26, you need to assign the number to return.
In my example, 1 returns 100, 2 returns 105....etc.

OR

You could construct a separate lookup table of letters with their
corresponding return value:

For lookup table in cells D1:E3
R............84
U............86
V............87
etc

and use: ="E"&VLOOKUP(MID(A1,5,1),D1:E3,2,0)


Does that give you something to work with?

***********
Regards,
Ron

 

[Gary''s Student]

I am not sure I have your pattern, but if you want the fifth character of A1
to produce the following text in B1:

A E66
B E67
C E68
D E69
E E70
F E71
G E72
H E73
I E74
J E75
K E76
L E77
M E78
N E79
O E80
P E81
Q E82
R E83
S E84
T E85
U E86
V E87
W E88
X E89
Y E90
Z E91
Then enter the following in B1:
="E" & CODE(MID(A1,5,1))+1

 

[Domenic]

If E86, E87, and E84 are actually cell references, change the formula to
the following...

=INDIRECT(VLOOKUP(MID(C1,5,1),$A$1:$B$3,2,0))

Hope this helps!

 

[nick s]

I am not sure I have your pattern, but if you want the fifth character of A1
to produce the following text in B1:

A E66
B E67
C E68
D E69
E E70
F E71
G E72
H E73
I E74
J E75
K E76
L E77
M E78
N E79
O E80
P E81
Q E82
R E83
S E84
T E85
U E86
V E87
W E88
X E89
Y E90
Z E91
Then enter the following in B1:
="E" & CODE(MID(A1,5,1))+1

this almost does what I need.
I need it to read the U,V,N,R,G,L and so on and many other letters and fill
in B1 with letters and numbers like E86, E44, E87. They are not in order as
in E86,E87,E88 ans so on.
It could be E44,E86,E90....

 

[Gary''s Student]

If the pattern is arbitrary, then Domenic's VLOOKUP is the best approach.

 

[nick s]

it seems that you may have helped me but I do not understand what you say, I
am sorry...
to be exact at what I am trying to do here are the exact letters and numbers
that I will be imputing and the exact letters and numbers I am needing to
show up.

RS23H1R109000 E55
RS23N1R109000 E65
RS23R1R109000 E74
RS23U1R109000 E86
RS23V1R109000 E87

So as you can see, if I type RS23H1R109000 in cell A1, I want cell B1 to
automaticly populate with E55

I need to be able to type any of the RS numbers in a cell and I need the
other cell to populate with the correct E number.

I hope that makes sense.....
Thanks again for your help, Nick

 

[nick s]

I re read your formula over and over and I finally got it to sink in that I
need to have a table to get this to work and it did, thank you.....

 

[nick s]

I found the right answer, Domenic had what I needed, thank you all.....

 

[Ron Coderre]

Now THAT was an astute observation, Domenic.
Kudos!

***********
Regards,
Ron

 

[nick s]

How would I ammend the statement below so that if there was no infomation
to return the resulting text would show blank instead of #N/A?

=VLOOKUP(MID(C1,5,1),$A$1:$B$3,2,0)

 

[Ron Coderre]

I can think of couple of ways..

You could extend the table so it includes every letter of the alphabet and
just put an apostrophe in the second column for those items (otherwise, it
returns a zero). This way has more table space, but the formula stays the
same.

OR

Try this
=IF(ISERROR(VLOOKUP(MID(C1,5,1),$A$1:$B$3,2,0)),"",VLOOKUP(MID(C1,5,1),$A$1:$B$3,2,0))


Does that help?

***********
Regards,
Ron

 

[nick s]

Thank you again, I have gone through the search function and I had been
trying all ways of getting this done and yours was the way to do it......