Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with info
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... |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with info
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! In article , nick s <nick wrote: 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... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with info
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 D84:D86. If this is the case, try using the following formula in B1: VLOOKUP(MID(A1,5,1),D84:E86,2) Hope this helps. JimB |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with info
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 "nick s" wrote: 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... |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with info
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 -- Gary's Student "nick s" wrote: 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... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with info
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! In article , Domenic wrote: 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! In article , nick s <nick wrote: 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... |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with inf
"Gary''s Student" wrote: 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 -- Gary's Student "nick s" wrote: 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... 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.... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with inf
If the pattern is arbitrary, then Domenic's VLOOKUP is the best approach.
-- Gary''s Student "nick s" wrote: "Gary''s Student" wrote: 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 -- Gary's Student "nick s" wrote: 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... 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.... |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with info
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" wrote: 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... |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with inf
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..... "Domenic" wrote: 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! In article , Domenic wrote: 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! In article , nick s <nick wrote: 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... |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with inf
I found the right answer, Domenic had what I needed, thank you all.....
"nick s" wrote: 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" wrote: 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... |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with inf
Now THAT was an astute observation, Domenic.
Kudos! *********** Regards, Ron "Domenic" wrote: 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! In article , Domenic wrote: 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! In article , nick s <nick wrote: 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... |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with inf
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" wrote: Now THAT was an astute observation, Domenic. Kudos! *********** Regards, Ron "Domenic" wrote: 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! In article , Domenic wrote: 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! In article , nick s <nick wrote: 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... |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with inf
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" wrote: 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" wrote: Now THAT was an astute observation, Domenic. Kudos! *********** Regards, Ron "Domenic" wrote: 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! In article , Domenic wrote: 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! In article , nick s <nick wrote: 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... |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with inf
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...... "Ron Coderre" wrote: 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" wrote: 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" wrote: Now THAT was an astute observation, Domenic. Kudos! *********** Regards, Ron "Domenic" wrote: 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! In article , Domenic wrote: 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! In article , nick s <nick wrote: 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... |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with info
On Sun, 27 Nov 2005 12:54:02 -0800, nick s
wrote: 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 In B1 enter the formula: ="E"&VLOOKUP(MID(A1,5,1),{"H",55;"N",65;"R",74;"U" ,86;"V",87},2,0) --ron |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pulling a Letter from a cell and filling another cell with inf
Ron, This worked also, thanks, I now have 2 ways of doing this.
"Ron Rosenfeld" wrote: On Sun, 27 Nov 2005 12:54:02 -0800, nick s wrote: 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 In B1 enter the formula: ="E"&VLOOKUP(MID(A1,5,1),{"H",55;"N",65;"R",74;"U" ,86;"V",87},2,0) --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling cell with color using barcode scanner | Excel Discussion (Misc queries) | |||
filling information from one cell and filling another. | Excel Worksheet Functions | |||
Copying and Filling Cell With Functions | Excel Discussion (Misc queries) | |||
filling a cell | New Users to Excel | |||
how do i get excel to see info in one cell, look at info in anoth. | Excel Discussion (Misc queries) |