Remove Non-AlphaNumeric Characters from String
The function below will allow us to remove a non alphanumeric character from an input string.
Be careful though, lengther the string, more time needed to evaluate each string on the sentence.
To bad that I don’t have any idea on how to use Regular Expression in Excel VBA, if we can use regex to replace all the unwanted character directly, it would be great 
Function GETALPHANUMERIC(text)
str_all = "abcdefghijklmnopqrstuvwxyz1234567890"
For lenstr = 1 To Len(text)
If InStr(str_all, LCase(Mid(text, lenstr, 1))) Then
GETALPHANUMERIC = GETALPHANUMERIC & Mid(text, lenstr, 1)
End If
Next
End Function
If somehow what we need is only the apha part without the numeric part or vice versa, simply adjusting the value of str_all variable will solve it.
Related Entries
Check If Excel Workbook is Already Open or Not
In my case, I work a lot with transferring data from current Excel Workbook to another Excel Workbook, and to be able to do that, of course I need to make sure whether the destination Workbook is already open or not. The following excel vba function assigned to check whether a workbook we need is open...
Convert Excel Column Number into Column Name
Update: Sorter version of the VBA function:Function Number2Char(c As Integer) As String Number2Char = Split(Cells(1, c).Address, "$")(1) End FunctionCourtesy of The Plaid Cow. ORFunction Number2Char(ByVal vNumber)Dim addr As String If vNumber 256 Then Exit Functionaddr = Range("A1").Offset(0, vNumber - 1).Address Number2Char...
How To Find Row Position of a Particular Text
Sometimes in Excel, we need to find the row position of a particular text, maybe to be able to paste certain of data right below the corresponding text or else. Very useful if we have an Excel Worksheet Template that we use a lot, with fix header on it.' Input param: Text we want to look...
How to get an Acronym using Excel VBA?
The purpose of the following example of Excel VBA macro is to get the acronym or an abbreviation of each of the first letter of any given words. Function Acronym(Words As Variant) As StringDim aWord() As String, ix As IntegeraWord = Split(Words.Value, " ") For...
Excel Extract Cell Comments
Excel VBA/Macro example below will extract comment from every cells with comment, and put the summary in the cell selected by user using Excel input box (read more about how to get cell reference using input box here). Sub CreateCommentsSummary()Dim rgComments As Range, rgCell As Range, rgOutput As Range, iRow As Integer,...
Valuable Resources
- Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
- 101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.
- Top Tips & Tricks to Get You Started in Microsoft Excel 2007
This is an ebook by Carol Bratt's, a Microsoft Certified Professional who has plenty of experience explaining things in simple English.
- Microsoft Office 2003/XP/2007 Training Videos
Discover an easy to use, hands-on interactive course that will teach you how to unleash the true power of Microsoft Office 2003/XP/2007.
If you found this page useful, please consider bookmark it using social media or add a link to this page.
