Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Ignoring letters behind numbers
This funtion works for me, if there are no letters behind numbers
=IF(ISNUMBER(B7),(IF(B74000, VLOOKUP(B7,Sheet1!A2:B263,2), "S")), "S") but some of the numbers have a letter or two behind them, which I want to ignore (eg. 4123HG i want to recognize as 4123).... Any Help? jeremy -- Message posted via http://www.officekb.com |
#2
|
|||
|
|||
If there are not more than 2 letters following and no preceding letters
Try something like =IF(ISNUMBER(B7),(IF(B74000, VLOOKUP(B7,Sheet1!A2:B263,2), "S")), IF(ISNUMBER( value(left(B7,len(b7)-1) ),(IF(value(left(B7,len(b7)-1)4000, VLOOKUP(value(left(B7,len(b7)-1),Sheet1!A2:B263,2), "S")), IF(ISNUMBER( value(left(B7,len(b7)-2) ),(IF(value(left(B7,len(b7)-2)4000, VLOOKUP(value(left(B7,len(b7)-2),Sheet1!A2:B263,2), "S")),"S")))) (I lost track of the parenthsis. You may need more or less of them at the end) "jeremy via OfficeKB.com" wrote: This funtion works for me, if there are no letters behind numbers =IF(ISNUMBER(B7),(IF(B74000, VLOOKUP(B7,Sheet1!A2:B263,2), "S")), "S") but some of the numbers have a letter or two behind them, which I want to ignore (eg. 4123HG i want to recognize as 4123).... Any Help? jeremy -- Message posted via http://www.officekb.com |
#3
|
|||
|
|||
Try:
=IF(LEFT(B7,LEN(B7)-(ISERROR(RIGHT(B7)*1)+ISERROR(MID(B7,LEN(B7)-1,1)*1)))*14000,VLOOKUP(LEFT(B7,LEN(B7)-(ISERROR(RIGHT(B7)*1)+ISERROR(MID(B7,LEN(B7)-1,1)*1)))*1,Sheet1!A2:B263,2),"S") HTH Jason Atlanta, GA "jeremy via OfficeKB.com" wrote: This funtion works for me, if there are no letters behind numbers =IF(ISNUMBER(B7),(IF(B74000, VLOOKUP(B7,Sheet1!A2:B263,2), "S")), "S") but some of the numbers have a letter or two behind them, which I want to ignore (eg. 4123HG i want to recognize as 4123).... Any Help? jeremy -- Message posted via http://www.officekb.com |
#4
|
|||
|
|||
I think this returns what you want:
=LEFT(A1,MAX((--ISNUMBER(--MID(A1,ROW(1:10),1))*ROW(1:10)))) Note: commit that array formula by pressing [Ctrl]+[Shift]+[Enter] If there will be more that 10 characters, change the 10's to an appropriate number. Does that help? -- Regards, Ron "jeremy via OfficeKB.com" wrote: This funtion works for me, if there are no letters behind numbers =IF(ISNUMBER(B7),(IF(B74000, VLOOKUP(B7,Sheet1!A2:B263,2), "S")), "S") but some of the numbers have a letter or two behind them, which I want to ignore (eg. 4123HG i want to recognize as 4123).... Any Help? jeremy -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
Actually, my first formula only strips text off the back of the cell value.
I believe this formula actually DOES do what you want: =IF(--(LEFT(A1,MAX((--ISNUMBER(--MID(A1,ROW(1:10),1))*ROW(1:10)))))4000, VLOOKUP(--(LEFT(A1,MAX((--ISNUMBER(--MID(A1,ROW(1:10),1))*ROW(1:10))))),Sheet1!A2:B263, 2), "S") Note: Commit that array formula by pressing [Ctrl]+[Shift]+[Enter] -- Regards, Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
no row numbers or column letters DISPLAYED | Excel Discussion (Misc queries) | |||
How do I sort letters before numbers in Excel? | Excel Discussion (Misc queries) | |||
VLOOKUP for a cell with both letters and numbers | Excel Discussion (Misc queries) | |||
Averaging noncontiguous numbers ignoring zeros? | Excel Worksheet Functions | |||
Finding Numbers with Cells that also contain letters | Excel Discussion (Misc queries) |