PDA

View Full Version : [SOLVED] Extracting Letters from the String



jonny
02-07-2019, 04:37 AM
Guys,
Need to extract all the letters within the string , separated by comma.
Now the code extracts first letters only.
Please your help with modifying an user defined function:

Function ExtractLetter(str As String)
Dim TDCode As String
Dim result As String

On Error GoTo ErrHandler:
With CreateObject("vbscript.regexp")
.Pattern = "[A-Z]+"
ExtractLetter = .Execute(str)(2)
End With
Exit Function
ErrHandler:
End Function


Input: HKJ234CBV546LL
Output (is): HKJ
Output (ought): HKJ,CBV,LL

Aflatoon
02-07-2019, 05:22 AM
Cross-posted (and answered) here: https://www.excelforum.com/excel-programming-vba-macros/1263431-extracting-letters-from-the-string.html

jonny
02-07-2019, 05:36 AM
Solved!


Function ExtractLetter(myStr As String)
' function that extract letters from string using Regular Expressions
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "[^A-Z]+"
ExtractLetter = Replace(Application.Trim(.Replace(myStr, " ")), " ", ",")
End With
End Function