How will I do Excel user-defined function to extract letters from string

[Wavit11]

How will I extract letters from this string
like... to:

AB-123456-45 AB
BCD-678901-23 BCD
E-23454 E

if I this bottom code extract numbers from a the same string:

Function ExtractNum(rCell As Range)
Dim iCount As Integer, i As Integer
Dim sText As String
Dim lNum As String

sText = rCell

For iCount = Len(sText) To 1 Step -1
If IsNumeric(Mid(sText, iCount, 1)) Then
i = i + 1
lNum = Mid(sText, iCount, 1) & lNum
End If

If i = 1 Then lNum = CInt(Mid(lNum, 1, 1))
Next iCount

ExtractNum = CLng(lNum)
End Function

 

[Guest]

Try the following macro

'------------------------------------------------------
Function ExtractNum(rCell As Range
Dim tmp
If Len(rCell.Value) > 0 The
For tmp = 1 To Len(rCell.Value
Select Case Asc(Mid(rCell.Value, tmp, 1)
Case 97 To 122, 65 To 9
ExtractNum = ExtractNum & Mid(rCell.Value, tmp, 1
End Selec
Nex
End I
End Functio
'------------------------------------------------------

Regards
Edwin Ta
(e-mail address removed)
http://www.vonixx.co


----- Wavit11 wrote: ----

How will I extract letters from this strin
like... to

AB-123456-45 A
BCD-678901-23 BC
E-23454

if I this bottom code extract numbers from a the same string

Function ExtractNum(rCell As Range
Dim iCount As Integer, i As Intege
Dim sText As Strin
Dim lNum As Strin

sText = rCel

For iCount = Len(sText) To 1 Step -
If IsNumeric(Mid(sText, iCount, 1)) The
i = i +
lNum = Mid(sText, iCount, 1) & lNu
End I

If i = 1 Then lNum = CInt(Mid(lNum, 1, 1)
Next iCoun

ExtractNum = CLng(lNum
End Functio