View Single Post
  #8   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

LOL - over to you for that one my friend - Should have checked it really.
Hope you are keeping well - Been a little while now :-)

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

"RagDyer" wrote in message
...
You really *don't* need an array entry for this formula Ken.

And this one will *not* return an error when the cell being referenced is
empty:

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789"))-1)

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Ken Wright" wrote in message
...
LOL, you're very welcome :-)

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

"Connie Martin" wrote in message
...
Thank you, Ken! That works wonderfully! What a pile of work that has
saved
me! I know that Excel can do anything. I just wish I knew how to make
it do
anything! Thanks again. Connie

"Ken Wright" wrote:

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