Ignoring letters behind numbers

  • Thread starter jeremy via OfficeKB.com
  • Start date
J

jeremy via OfficeKB.com

This funtion works for me, if there are no letters behind numbers

=IF(ISNUMBER(B7),(IF(B7>4000, 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
 
B

bj

If there are not more than 2 letters following and no preceding letters
Try something like
=IF(ISNUMBER(B7),(IF(B7>4000, 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)
 
J

Jason Morin

Try:

=IF(LEFT(B7,LEN(B7)-(ISERROR(RIGHT(B7)*1)+ISERROR(MID(B7,LEN(B7)-1,1)*1)))*1>4000,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
 
R

Ron Coderre

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

Ron Coderre

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]
 
Top