Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 16

Thread: Extract Alphanumerics From String

  1. #1
    Join Date
    15th June 2007
    Posts
    13

    Extract Alphanumerics From String

    Hi,

    I have many strings of arbitrarily length. Each string always
    has one number (0-9) component and one alpha-character (A-Z) component.
    The order of the components in the string is entirely random.
    Sometimes numbers come first and sometimes letters/characters.

    Also, the length of each separate component is also varies and
    there is no fixed rule to how long it will be each time.

    Examples are:

    String: RGH45 Alpha: RGH Num: 45
    String: 4589THF Alpha:THF Num: 4589
    String: FGGFFF56464645464 Alpha:FGGFFF Num: 56464645464
    String: sdgdfgdfg874645 Alpha:sdgdfgdfg Num: 874645
    String: 54sfsdfdsf Alpha:sfsdfdsf Num: 54
    String: s54654646 Alpha:s Num: 54654646

    etc..

    I want to have two strings. One called Alpha and One called Num
    I want to have the alpha-character component to be extracted from the string
    and set to Alpha and the numerical component to be extracted & set to Num.

    How/what is the Excel Macro VB code to accomplish this from within the macro?


    Thanks a lot!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2007
    Posts
    759

    Re: Desire Versatile Alphanumeric Extractor From Any String

    Is this what you need?

    VB:
    Public Function ExtractNumeric(TextString As String) As String 
        Dim x As Long 
        Dim sDigit As String 
         
        ExtractNumeric = vbNullString 
        For x = 1 To Len(TextString) 
            sDigit = Mid(TextString, x, 1) 
            If sDigit >= "0" And sDigit <= "9" Then 
                ExtractNumeric = ExtractNumeric & sDigit 
            End If 
        Next x 
    End Function 
     
    Public Function ExtractAlpha(TextString As String) As String 
        Dim x As Long 
        Dim sChar As String 
         
        ExtractAlpha = vbNullString 
        For x = 1 To Len(TextString) 
            sChar = Mid(TextString, x, 1) 
            If sChar Like "[a-zA-Z]" Then 
                ExtractAlpha = ExtractAlpha & sChar 
            End If 
        Next x 
    End Function 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th April 2006
    Posts
    890

    Re: Desire Versatile Alphanumeric Extractor From Any String

    This is an adaption of turtle44's response who helped me finish my code with his. Thanks turtle44, I was stuck for a while.

    VB:
    Sub FindText() 
         
        Dim ws As Worksheet 
        Dim x As Integer, y As Integer, z As Integer 
        Dim w As String, digit As String, alpha As String 
         
        Set ws = Worksheets("Sheet1") 
         
        For x = 2 To 7 
            digit = vbNullString 
            alpha = vbNullString 
            y = Len(Trim(ws.Range("B" & x).Value)) 
            For z = 1 To y 
                w = Mid(ws.Range("B" & x), z, 1) 
                If IsNumeric(w) Then 
                    digit = digit & w 
                Else 
                    alpha = alpha & w 
                End If 
            Next 
            ws.Range("C" & x).Value = alpha 
            ws.Range("D" & x).Value = digit 
        Next x 
    End Sub 
    
    

    EDIT - I noticed that I had
    VB:
    Trim(Len(ws.Range("B" & x).Value)) 
     
    which does Not make sense. You would want To eliminate the spaces first I think. It should be 
     
    Len(Trim(ws.Range("B" & x).Value)) 
    
    
    This change is not reflected in the attached workbook
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by bryce; July 27th, 2007 at 04:17.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  5. #5
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,505

    Re: Extract Alphanumerics From String

    Numbers:
    =AlphaNum(A1,True)

    Else
    =AlphaNum(A1,False)
    VB:
    Function AlphaNum(txt As String, Optional numOnly As Boolean = True) 
        With CreateObject("VBScript.RegExp") 
            .Pattern = IIf(numOnly=True,"\d+",\D+") 
            AlphaNum = .exexute(txt)(0) 
        End With 
    End Function 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,548

    Re: Extract Alphanumerics From String

    Jindon - nice code mate...

    I wasnt the OP, but took a look at the code... Two typos in the code as far as I can see (or at least needed to be fixed to run without compile error -> a double quote missing from \D and "execute" typo)...

    VB:
    Function AlphaNum(txt As String, Optional numOnly As Boolean = True) As String 
         
        With CreateObject("VBScript.RegExp") 
            .Pattern = IIf(numOnly = True, "\d+", [B]"[/B]\D+") 
            AlphaNum = .[B]Execute[/B](txt)(0) 
        End With 
    End Function 
    
    
    Just curious, is it possible to amend this to get this to return ALL Alpha characters and / or ALL numerics?

    Currently 123456abcdefg098762z1x2 would return 123456 (and ignore 09876212)...

    This could be a keeper.
    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Extract Alphanumerics From String

    Ger, to get all numeric from Alphanumerics see It allows the option to take decimals and/or negative numbers.

  8. #8
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,505

    Re: Extract Alphanumerics From String

    Thanks Ger

    VB:
    Function AlphaNum(txt As String, Optional numOnly As Boolean = True) As String 
         
        With CreateObject("VBScript.RegExp") 
            .Pattern = IIf(numOnly = True, "-?\d+(\.\d+)?", "\D+") 
            AlphaNum = .Execute(txt)(0) 
        End With 
    End Function 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,548

    Re: Extract Alphanumerics From String

    Thanks Dave - I had a look at that alright... but for the moment, I'm trying to learn Regular Expressions - just because I'm slightly nuts right now... persistent rain does that to a man

    Jindon, sorry Mate, I just couldnt get your code to return all instances of Letters (or all instances of numbers). Your code would only return the first "Group" of digits or letters... As I say, I'm only learning Regexp at the moment, and did some research into it. I found a great link here -
    http://www.vbaexpress.com/kb/getarticle.php?kb_id=68

    (with downloadable examples too....)

    After some messing with the examples at the above site, the following code seemed to do the trick for me.

    VB:
    Function AlphaNum(txt As String, Optional numOnly As Boolean = True) As String 
        Dim my_Collection As Object 
        Dim my_match As Object 
        Dim my_string As String 
        Dim RegExp As Object 
         
        With CreateObject("VBScript.RegExp") 
            .Global = True 
            .Pattern = IIf(numOnly = True, "[\d+]", "[\D+]") 
            Set my_Collection = .Execute(txt) 
            For Each my_match In my_Collection 
                my_string = my_string & my_match 
            Next 
            AlphaNum = my_string 
        End With 
    End Function 
    
    
    As I say, dont know much about Regular Expressions, so I am not sure if thats the quickest Regular Expression to do the job...

    Cheers guys,
    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Extract Alphanumerics From String

    Ger, what do you mean by "Regular Expressions" ?

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Extract Specific Values From Alphanumerics In Range
    By peter_435 in forum EXCEL HELP
    Replies: 2
    Last Post: September 24th, 2008, 12:34
  2. Extract Numeric Value From String
    By ghh3rd in forum EXCEL HELP
    Replies: 1
    Last Post: March 16th, 2007, 05:21
  3. extract islike string
    By Robts in forum EXCEL HELP
    Replies: 5
    Last Post: June 24th, 2006, 06:03
  4. Extract The Number From A String
    By Michael Avidan in forum EXCEL HELP
    Replies: 13
    Last Post: November 23rd, 2005, 03:27
  5. VBA to extract first two characters from a string
    By damianmcl78 in forum EXCEL HELP
    Replies: 4
    Last Post: January 10th, 2005, 18:05

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
  •  
porno