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

[Alejandro Rodriguez]

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,

 

[jindon]

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

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

 

[Trebor76]

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

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

 

[jindon]

OOps

Function AlphaNum(txt As String, Optional Alpha As Boolean = True) String

should be

Function AlphaNum(txt As String, Optional Alpha As Boolean = True) As String

 

[Alejandro Rodriguez]

jindon, thanks so much!

works really fantastic.


may i also request your help with the next post?
http://www.mrexcel.com/forum/showthread.php?t=346629

once again, thanks so much for your cooperation.

 

[Alejandro Rodriguez]

Robert, also works!. thank you.

 

[jesma12]

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.

 

[Trebor76]

Hi jesma12,

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

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

 

[jesma12]

Thank you so much!!

 

[Trebor76]

You're welcome