Look for the 6th and 7th character in a cell

[Tartesos]

Hello again.

I was wondering if there is a way, using a formula, to look in a cell and depending on the carachter on the 6th and or 7th place to output a specific information in another cell.

Lets say we have a number, REF345678567.

I need to have a formula that will look that number and when it finds the number 5 (6th carac.) it will fill another cell with information from another columm.

It needs to look also for the 7th carac., in this case 6, and put information on another cell from a columm.

Can anyone help me with this ??

Thanks and regards.

 

[Smitty]

Re: Look for the 6th and 7th carachter in a cell

If you're always looking for those positions you can use =MID(A1,6,1) & =MID(A1,7,1)

HTH,

 

[Tartesos]

Re: Look for the 6th and 7th carachter in a cell

Yes, it will be always be that 6th and 7th place. The thing is that I'm not very good with excel. Can you give a complet exemp. of this?
Ummm, how do I start this formula? I need the formula to look in, lets say A1, search that cell and when it finds a caract. on the 6th place to output a value from another, lets say list, to B1 and when it finds the 7th caract. to do the same on C1. How do I do this ... I'm green , been trying for couple of days with not luck.

Thanks

 

[texasalynn]

Excel Workbook
	K	L	M
9	REF345678567	5	6
Sheet3

Excel 2003

Cell Formulas
Range	Formula
L9	=MID(K9,6,1)
M9	=MID(K9,7,1)

 

[Smitty]

Re: Look for the 6th and 7th carachter in a cell

The formulas I posted will do that. Put the first in B1, the second in C1.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">REF345678567</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=MID(<font color="Blue">$A1,6,1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=MID(<font color="Blue">$A1,7,1</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Tartesos]

Re: Look for the 6th and 7th carachter in a cell

Ok, that does the trick of getting the 6th and 7th caract. on the number. But, what about telling excel that if the 6th caract. is 5, on this case, to fill another cell with information relate to that 5.
Lets say that 5 is a provider. 5 = Telecom.
I want the formula to look on the number in A1 and if it finds a 5 on the 6th place to fill in the cell B1 with the name Telecom that is the designation name for the 5 on that number when it is find on the 6th place.
Any ideas??


Thanks again.

 

[texasalynn]

so how many different ones will you have?
5 = Telecom
6 = ?

Excel Workbook
	K	L	M
9	REF345678567	Telecom	Other
Sheet3

Excel 2003

Cell Formulas
Range	Formula
L9	=CHOOSE(MID(K9,6,1),0,0,0,0,"Telecom")
M9	=CHOOSE(MID(K9,7,1),0,0,0,0,"Telecom","Other")

 

[Tartesos]

I have about 200 options

 

[texasalynn]

ooooh no! LOL

Then I would suggest you great a table with those 200, then you can use a vlookup.

=vlookup(MID($A1,6,1),tabledata,2,false)
=vlookup(MID($A1,7,1),tabledata,2,false)

Replace Tabledata with the range area that you setup

 

[Dryver14]

If you lookup Charachter 5 or 6 in a cloumn this will return you 20 options how does this relate to 200 options?