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

Answer: Copy only letters, not numbers
 

Hi Connie,

Yes, it is definitely possible to extract only the letters from your product codes using a formula in Excel. Here are the steps you can follow:

1. Assuming your product codes are in column A, you can enter the following formula in cell B1:
   
   
   Formula:

   ---

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


   ---

2. Press enter and the formula will extract only the letters from the first product code in column A.
   
3. Now, you can copy this formula down the column by selecting cell B1 and dragging the fill handle (the small square at the bottom right corner of the cell) down to the last row of your data.
   
4. The formula will automatically adjust for each row and extract only the letters from each product code.

Let me know if you have any questions or if there's anything else I can help you with.

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

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

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

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

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

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

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

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