A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Copy only letters, not numbers



 
 
Thread Tools Display Modes
  #1  
Old May 11th 09, 08:50 PM posted to microsoft.public.excel.worksheet.functions
Connie Martin
external usenet poster
 
Posts: 251
Default Copy only letters, not numbers

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
Ads
  #2  
Old May 11th 09, 08:55 PM posted to microsoft.public.excel.worksheet.functions
Ken Wright
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



  #3  
Old May 11th 09, 09:15 PM posted to microsoft.public.excel.worksheet.functions
Connie Martin
external usenet poster
 
Posts: 251
Default Copy only letters, not numbers

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

>
>
>

  #4  
Old May 11th 09, 09:45 PM posted to microsoft.public.excel.worksheet.functions
Ken Wright
external usenet poster
 
Posts: 634
Default Copy only letters, not numbers

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

>>
>>
>>



  #5  
Old May 11th 09, 10:09 PM posted to microsoft.public.excel.worksheet.functions
Bill Kuunders
external usenet poster
 
Posts: 303
Default Copy only letters, not numbers

I assume that your first code is in A2

You can use a function
as described by Kevin Backmann
copy and paste this into a module in the visual basic editor.
press <alt><F11> to get there
insert a new module and paste the lines below into the right hand window



Function ExtractAlpha(varVal As Variant) As String

Dim intLen As Integer
Dim strVal As String
Dim i As Integer
Dim strChar As String

intLen = Len(varVal)

For i = 1 To intLen
strChar = Mid$(varVal, i, 1)
If Asc(strChar) >= 65 And Asc(strChar) <= 90 Or _
Asc(strChar) >= 97 And Asc(strChar) <= 122 Then
strVal = strVal & strChar
End If
Next i

ExtractAlpha = strVal

End Function



then
enter =ExtractAlpha(A2) in B2

To get writ of the x's at the end
You will have to use a normal if- formula in C2

=IF(RIGHT(B2,1)="x",MID(B2,1,LEN(B2)-1),B2)

extend B2 and C2 as far as you need to by left click and drag down of the
right hand bottom corners of the cells after the mouse pointer has changed
to a "+" sign


--
Greetings from New Zealand


"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



  #6  
Old May 11th 09, 11:50 PM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,574
Default Copy only letters, not numbers

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

>
>



  #7  
Old May 12th 09, 03:05 AM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein
external usenet poster
 
Posts: 5,934
Default Copy only letters, not numbers

Just thought you might find it interesting to know that this line...

If Asc(strChar) >= 65 And Asc(strChar) <= 90 Or _
Asc(strChar) >= 97 And Asc(strChar) <= 122 Then

can be replaced with this one...

If strChar Like "[A-Za-z]" Then

--
Rick (MVP - Excel)


"Bill Kuunders" > wrote in message
...
>I assume that your first code is in A2
>
> You can use a function
> as described by Kevin Backmann
> copy and paste this into a module in the visual basic editor.
> press <alt><F11> to get there
> insert a new module and paste the lines below into the right hand window
>
>
>
> Function ExtractAlpha(varVal As Variant) As String
>
> Dim intLen As Integer
> Dim strVal As String
> Dim i As Integer
> Dim strChar As String
>
> intLen = Len(varVal)
>
> For i = 1 To intLen
> strChar = Mid$(varVal, i, 1)
> If Asc(strChar) >= 65 And Asc(strChar) <= 90 Or _
> Asc(strChar) >= 97 And Asc(strChar) <= 122 Then
> strVal = strVal & strChar
> End If
> Next i
>
> ExtractAlpha = strVal
>
> End Function
>
>
>
> then
> enter =ExtractAlpha(A2) in B2
>
> To get writ of the x's at the end
> You will have to use a normal if- formula in C2
>
> =IF(RIGHT(B2,1)="x",MID(B2,1,LEN(B2)-1),B2)
>
> extend B2 and C2 as far as you need to by left click and drag down of the
> right hand bottom corners of the cells after the mouse pointer has changed
> to a "+" sign
>
>
> --
> Greetings from New Zealand
>
>
> "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

>
>


  #8  
Old May 13th 09, 09:48 PM posted to microsoft.public.excel.worksheet.functions
Ken Wright
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
>>>>
>>>>
>>>>

>>
>>

>
>



  #9  
Old May 15th 09, 12:37 AM posted to microsoft.public.excel.worksheet.functions
RagDyeR
external usenet poster
 
Posts: 3,574
Default Copy only letters, not numbers

Yes, it's been a while.<g>

I remember spending hours in these groups ... now it's minutes ... if at
all,
and mostly now ... it's not at all.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ken Wright" > wrote in message
...
> 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
>>>>>
>>>>>
>>>>>
>>>
>>>

>>
>>

>
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy first few letters from a cell Mat Excel Worksheet Functions 9 January 18th 08 07:07 PM
Sorting - cells containing numbers, numbers and letters Gunny Excel Discussion (Misc queries) 5 July 16th 06 01:22 AM
Copy the first two or three letters of a cell to another one Jerry Excel Discussion (Misc queries) 4 February 22nd 06 07:29 AM
create self-generating numbers with letters and numbers cxlough41 Excel Discussion (Misc queries) 11 January 4th 06 02:16 AM
Letters = Numbers oberon.black Charts and Charting in Excel 1 November 11th 05 01:52 AM


All times are GMT +1. The time now is 03:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.