Vlookup to sort on letters, not numbers

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hell
I am a newbie. I am using vlookup (ab1,ac1:ad122,2,0) to sort an input consisting of one or two letters - SR, GY, W, Z, etc. This works well until there is a one or two number suffix immediately following the letters. The numbers cause #N/A. Is there a way of using a wildcard to make the sort disregard the figures
Thanks in advanc
David
 
Hi david
if you're only searching for the first two letters try
=vlookup(ab1,LEFT(ac1:ad122,2),2,0)

Frank
 
Hi Fran
Regret suggestion gives #VALUE! on one or two letters followed by number. Am I missing something
David
 
Hi David
sorry I forgot to mention that you have to enter this formula as array
formula. That is hitting CTRL+SHIFT+ENTER and not only ENTER then
entering the formula

Frank
 
And just to add a non-array solution (using a wildcard):
=vlookup(ab1 & "*",ac1:ad122,2,0)

Frank
 
Hello Fran
Sorry, but neither work when numbers present - ok when not, which was original problem
Thanks for effor
David
 
David said:
Hello Frank
Sorry, but neither work when numbers present - ok when not, which was
original problem. Thanks for effort
David
Click to expand...

Hi David
if you like send me your spreadsheet. Both formulas should work but
maybe I'm missing some of your requirements
Frank
 
Here is the answer to my own query
I was searching sites and found on exceltip.com a question about splitting A123B456C789 into component parts A123 in one cell, B456 in another cell and C789 in a third cell. Peo Sjoblom replied to this on 31/1/04 and, while his reply wasn't directed at my query, I played around with his three formulas and have got one to work
Assuming one or two letters followed by x figures in A1, following in B1. =LEFT(A1,MATCH,(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("2:"&LEN(A1))),1)),0)) entered as array. This strips out x number suffixes
I hope this helps others progress with this great program
David
 
Back
Top