Copy only letters, not numbers

  • Thread starter Thread starter Connie Martin
  • Start date Start date
C

Connie Martin

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
 
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&"0123456789"))>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........................
 
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&"0123456789"))>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........................
 
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
 
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
 
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
 
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
 
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&"0123456789"))-1)
 
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&"0123456789"))-1)
 
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
 
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
 
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................
 
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................
 
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.
 
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.
 
Back
Top