How to extract letters from the string
Requirement:
The user needs 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:
Code:
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
Solution:
Code:
Function RemoveNumbers(Txt As String) As String
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(Txt, " ")
End With
End Function
Should you need to insert commas between the letters ... you could add following instruction :
Code:
RemoveNumbers = Replace(Application.WorksheetFunction.Trim(RemoveNumbers), " ", ",")
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create a custom function to extract integers from a simple 11 character string |
| How to add digit or replace last digit in string based on criteria |
| How to extract text from a string before a last specified character |
| How to extract text string |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
