+ Reply to Thread
Results 1 to 8 of 8

Extract and return only alpha, numeric and also specified characters from a parameter str

Hybrid View

brucemc777 Extract and return only... 01-19-2017, 12:51 PM
LJMetzger Re: Extract and return only... 01-20-2017, 10:46 AM
brucemc777 Re: Extract and return only... 01-20-2017, 02:45 PM
brucemc777 Re: Extract and return only... 01-20-2017, 02:09 PM
LJMetzger Re: Extract and return only... 01-20-2017, 03:14 PM
brucemc777 Re: Extract and return only... 01-20-2017, 05:10 PM
LJMetzger Re: Extract and return only... 01-21-2017, 11:36 AM
brucemc777 Re: Extract and return only... 01-23-2017, 11:27 AM
  1. #1
    Forum Contributor
    Join Date
    03-05-2007
    Location
    Falmouth, VA now, Palm Bay, FL for 2 yrs, was Colorado Springs, CO for ten years; Cedark Park, TX; Zeeland, MI; Wilmette, IL; Princeton Junction, NJ; NY, NY
    MS-Off Ver
    365
    Posts
    615

    Re: Extract and return only alpha, numeric and also specified characters from a parameter

    In that I am always revising what I write I suppose there are some modifications that can be made to the following to make it more efficient, but I wanted to post this in the event anyone else might be looking for a similar solution-

    Many thanks to Peter Albert and LJMetzger!

    Function ExtractOnly(strSource As String, Optional strLimit As String, Optional strExceptions As String) As String
    ' Inspired by original code by Peter Albert at http://stackoverflow.com/questions/15723672/how-to-remove-all-non-alphanumeric-characters-from-a-string-except-period-and-sp and
    ' changes suggested by LJMetzger at http://www.excelforum.com/showthread.php?t=1170465&p=4564494#post4564494
    
    ' strSource is the string you are feeding the function
    ' strExceptions is a string of additional characters which you wish to include
    
    ' If Optional strLimit is missing or is "an" then returns both alpha and numeric
    ' If Optional strLimit is an "a" then returns strictly alpha
    ' If Optional strLimit is an "n" then returns strictly numeric
    
        Dim i As Long
        Dim strCase As String
        Dim strResult As String
        Dim c As String
        
        If strLimit = "" Then strLimit = "an"
    
        If strLimit = "an" Then
            strCase = "A-Za-z0-9"
        ElseIf strLimit = "a" Then
            strCase = "A-Za-z"
        ElseIf strLimit = "n" Then
            strCase = "0-9"
        End If
        
        If Len(strExceptions) > 0 Then
            For i = 1 To Len(strExceptions)
                strCase = strCase & Mid(strExceptions, i, 1)
            Next i
        End If
    
      For i = 1 To Len(strSource)
        c = Mid(strSource, i, 1)
        If c Like "[" & strCase & "]" Then
          strResult = strResult & Mid(strSource, i, 1)
        End If
      Next i
    
        ExtractOnly = strResult
    
    End Function
    Last edited by brucemc777; 01-20-2017 at 05:44 PM. Reason: Forgot to add the assignment if strLimit was empty.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 03-17-2016, 08:55 AM
  2. Separating Alpha and Numeric Characters
    By genoa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-22-2015, 04:57 AM
  3. Separating alpha, numeric and other characters
    By ldg in forum Excel General
    Replies: 10
    Last Post: 08-04-2015, 02:16 PM
  4. Separate Alpha and numeric characters
    By sivdin in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-09-2013, 02:50 AM
  5. Replies: 2
    Last Post: 06-18-2010, 05:10 PM
  6. only extract numeric value from alpha numeric cell
    By Fam via OfficeKB.com in forum Excel General
    Replies: 5
    Last Post: 04-26-2006, 01:55 PM
  7. Can you ID a cell that has both Alpha AND Numeric characters?
    By Phil in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-18-2006, 04:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1