View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ken Wright Ken Wright is offline
external usenet poster
 
Posts: 634
Default Copy only letters, not numbers

Assuming your data is in cell A1:A100, in cell B1 put the following formula:

=LEFT(A1,IF(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789"))LEN(A1),0,MIN(SEARCH({0,1,2,3,4,5,6,7 ,8,9},A1&"0123456789")))-1)

but make sure you ARRAY ENTER it, by using CTRL+SHIFT+ENTER

If successful you will see curly braces appear around the formula, eg
{=formula}

Now just copy and then paste across B2:B100

Regards
Ken........................


"Connie Martin" wrote in message
...
I have a huge long column of text---product codes, to be exact, that could
look something like this: CNUB275X3 or PB24X275. What I need is a
formula,
which I will put in the column beside this one, that will look at the code
and put only the letters up to the first number. So, in this case it
would
put CNUB in the first row's cell and then PB for the next one. Is this
possible? Connie