Share via

Extract Text from Alphanumeric

Anonymous
2017-07-01T07:31:41+00:00

Split from this thread.

Ash2ra3f4

L1O23V4EU

result is Ashraf,LOVEU

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

10 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Ashish Mathur 100.8K Reputation points Volunteer Moderator
    2017-07-01T23:38:40+00:00

    Hi,

    Try this

    1. Suppose your entries are in range A2:A100
    2. In range B2:B4, type the result
    3. Select B2:B100 and go to Data > Flash Fill

    Hope this helps.

    2 people found this answer helpful.
    0 comments
  3. Anonymous
    2017-07-02T00:03:35+00:00

    Hi,Rick thanks for your great comment

    0 comments
  4. Anonymous
    2017-07-03T02:59:42+00:00

    Hi Mey,

    you might mark or vote in one or more answers, since your issue has been solved.

    1 person found this answer helpful.
    0 comments
  5. Anonymous
    2017-07-03T11:14:53+00:00

    For a formula approach, in Excel 2007 or later, you can use:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,

    0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

    You can use a similar approach to extract the digits, while retaining leading zero's.

    For a VBA (UDF) approach, you can use these two formulas -- They cycle through either the digits, or the letters, depending on what you want to remove.

    Option Explicit

    Function RemDigits(S As String) As String

        Dim I As Long

        Dim str As String

    str = S

    For I = 0 To 9

        str = Replace(str, I, "")

    Next I

    RemDigits = str

    End Function

    Function RemText(S As String) As String

        Dim I As Long

        Dim str As String    

    str = S

    For I = 1 To 26 'for clarity as there are 26 letters

        str = Replace(str, Chr(I + 64), "", compare:=vbTextCompare)

    Next I

    RemText = str

    End Function

    1 person found this answer helpful.
    0 comments