View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bill Kuunders Bill Kuunders is offline
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