Extract Text from Alphanumeric

Question

Split from this thread.

Ash2ra3f4

L1O23V4EU

result is Ashraf,LOVEU

Answer 1

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the code module:

Function ExtractText(s As String) As String

    Dim i As Long

    For i = 1 To Len(s)

        If Not Mid(s, i, 1) Like "[0-9]" Then

            ExtractText = ExtractText & Mid(s, i, 1)

        End If

    Next i

End Function

Press Alt+F4 to close the Visual Basic Editor.

Usage:

Let's say Ash2ra3f4 is in cell A1.

In another cell, enter the formula    =ExtractText(A1)

Make sure that you save the workbook as a macro-enabled workbook (.xlsm) and that you allow macros when you open it.

Answer 2

HansV,thanks

Answer 3

        If Not Mid(s, i, 1) Like "[0-9]" Then

Here are two other ways in which you could write the above "If..Then" statement from your posted code...

1. If Not Mid(s, i, 1) Like "#" Then
2. If Mid(s, i, 1) Like "[!0-9]" Then

Of the three total methods, I personally favor the last one.

Answer 4

Hi.  If you are going down a list of values, here's another option:

(Example:  A1:A10)

Sub RemoveNumbers()

    Dim Cell As Range

   

    With CreateObject("VBScript.RegExp")

    .Pattern = "\d"

    .Global = True

    .IgnoreCase = True

    .MultiLine = True

   

    For Each Cell In [A1:A10].Cells

        Cell = .Replace(Cell.Text, vbNullString)

    Next

    End With

End Sub

Why not something simpler, like this...

Sub RemoveNumbers()

    Dim N As Long

    For N = 0 To 9

        [A1:A10].Replace N, "", xlPart, , , , False, False

    Next

End Sub

Although, in case the ranges could be large, I'd turn screen updating off...

Sub RemoveNumbers()

    Dim N As Long

    Application.ScreenUpdating = False

    For N = 0 To 9

        [A1:A10].Replace N, "", xlPart, , , , False, False

    Next

    Application.ScreenUpdating = True

End Sub