Results 1 to 10 of 10

Extract text from alphanumeric strings. Formula, udf or code.

This is a discussion on Extract text from alphanumeric strings. Formula, udf or code. within the Excel Questions forums, part of the Question Forums category; hi, I have many registers with alphanumeric strings. I want to extract only the text from these cells. I have: ...

  1. #1
    New Member
    Join Date
    Jul 2005
    Posts
    40

    Default Extract text from alphanumeric strings. Formula, udf or code.

    hi,

    I have many registers with alphanumeric strings.
    I want to extract only the text from these cells.

    I have:
    A2:FL9O7WER
    A3:8T0AB765LE
    A4:9FL7O8O4R4

    and I need:
    B2: FLOWER
    B3: TABLE
    B4: FLOOR

    How to do this?. With a formula, udf or code?

    Please help. Appreciate in advance your cooperation.
    thanks,

  2. #2
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: Extract text from alphanumeric strings. Formula, udf or code.

    UDF
    1) Hit Alt + F11 to open VBE
    2) go to [Insert] - [Module] then paste the code onto the right pane
    3) hit Alt + F11 again to get back to Excel
    Use in cell like
    =AlphaNum(A1,True) '<- True for Alphabets, False for Numbers
    Code:
    Function AlphaNum(txt As String, Optional Alpha As Boolean = True) String
    With CreateObject("VBScript.RegExp")
        .Pattern = IIf(Alpha, "\d+", "\D+")
        .Global = True
        AlphaNum = .replace(txt,"")
    End With
    End Function

  3. #3
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    2,368

    Default Re: Extract text from alphanumeric strings. Formula, udf or code.

    Hi there,

    I'd say the udf way is the best.

    Not sure if you already know how to do this, but the following five steps will put the code into a module from which you can run my udf like any other native excel formula - i.e. =TextOnly(A2):

    1. Copy (Ctrl + C) my code
    2. Open the VBA editor (Alt + F11)
    3. From the Insert menu click Module
    4. Paste (Ctrl + V) my code from step 1 above into the blank module
    5. From the File menu click Close and Return to Microsoft Excel

    HTH

    Robert

    Code:
    Function TextOnly(rng As Range) As String
    
    Dim intChrCnt As Integer
        For intChrCnt = 1 To Len(rng)
            If IsNumeric((Mid$(rng, intChrCnt, 1))) = False Then
                TextOnly = TextOnly & Mid$(rng, intChrCnt, 1)
            End If
        Next
        
    End Function

  4. #4
    MrExcel MVP
    Join Date
    Aug 2004
    Location
    Tokyo, Japan
    Posts
    16,995

    Default Re: Extract text from alphanumeric strings. Formula, udf or code.

    OOps
    Code:
    Function AlphaNum(txt As String, Optional Alpha As Boolean = True) String
    should be
    Code:
    Function AlphaNum(txt As String, Optional Alpha As Boolean = True) As String

  5. #5
    New Member
    Join Date
    Jul 2005
    Posts
    40

    Smile Re: Extract text from alphanumeric strings. Formula, udf or code.

    jindon, thanks so much!

    works really fantastic.


    may i also request your help with the next post?
    Save as Text with Unicode and Tab Delimited

    once again, thanks so much for your cooperation.

  6. #6
    New Member
    Join Date
    Jul 2005
    Posts
    40

    Default Re: Extract text from alphanumeric strings. Formula, udf or code.

    Robert, also works!. thank you.

  7. #7
    New Member
    Join Date
    Jan 2009
    Location
    PA
    Posts
    44

    Default Re: Extract text from alphanumeric strings. Formula, udf or code.

    I used the above code but i need a small variation. If there is no text found, i need it to return the word "cups". Thank you in advance.

  8. #8
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    2,368

    Default Re: Extract text from alphanumeric strings. Formula, udf or code.

    Hi jesma12,

    Though it would be advisable to have started a new thread with a link back to this one, see how this goes:

    Code:
    Function AlphaExtract(txt As String, Optional varDefaultVal As Variant) As String
    
        'Use in a cell like any Excel function, i.e. _
        =AlphaExtract(A4,"cups")
        'will extract the text from A4 or return 'cups' _
        (though this can be text or a value) if there's no text in A4.
    
        With CreateObject("VBScript.RegExp")
            .Pattern = ("\d+")
            .Global = True
            AlphaExtract = .Replace(txt, "")
        End With
        
        If AlphaExtract = "" Then AlphaExtract = varDefaultVal
        
    End Function
    HTH

    Robert

  9. #9
    New Member
    Join Date
    Jan 2009
    Location
    PA
    Posts
    44

    Default Re: Extract text from alphanumeric strings. Formula, udf or code.

    Thank you so much!!

  10. #10
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    2,368

    Default Re: Extract text from alphanumeric strings. Formula, udf or code.

    You're welcome

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com