Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove Alphabetic Characters (Excel 2003)

    Is there a way to get rid of only alphabetic characters using formula, no matter where they appear in the cell?
    For example, if the cell contains "ABC123," I want to get rid of "ABC" and have just "123" remaining.
    Similarly, for cell with "X7y8Z9" should become "789" and "#4P5*%" should become "#45*%"

    Regards, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove Alphabetic Characters (Excel 2003)

    Hi Francis

    If you are happy with a UDF try this:

    Option Explicit
    Function NoAlpha(str)
    Dim N As Integer, i As String
    i = ""
    For N = 1 To Len(str)
    If IsNumeric(Mid(str, N, 1)) Then
    i = i & Mid(str, N, 1)
    If Mid(str, N + 1, 1) = "." Then i = i & "."
    End If
    Next
    If i = "" Then
    NoAlpha = i
    Exit Function
    End If
    NoAlpha = CDbl(i)
    End Function
    Jerry

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Remove Alphabetic Characters (Excel 2003)

    I don't think it can be done with a formula. It will take a defined function. Add this a module in the workbook or your personal.xls (See WMVP <!profile=LegareColeman>LegareColeman<!/profile>'s Personal.xls Tutorial (All) for more info on using Personal.xls)

    <pre>Option Explicit
    Function StripAlphabet(sWord As String)
    Dim x As Long
    Dim sTemp As String
    Dim sCharacter As String
    sWord = UCase(sWord) 'Convert to Uppercase
    sTemp = "" 'Set originall as null
    For x = 1 To Len(sWord) 'Act on each letter
    sCharacter = Mid(sWord, x, 1)
    If Asc(sCharacter) < 65 Or _
    Asc(sCharacter) > 90 Then '65-90 are ASCII "A"-"Z"
    'sCharacter is NOT a LETTER
    sTemp = sTemp & sCharacter
    End If
    Next x
    StripAlphabet = sTemp
    End Function</pre>


    Then you can add in a cell (eg B1):
    =stripalphabet(A1)

    to display what you desire in B1 stripping A1 or even call it in a VBA routine if you want

    Steve

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Remove Alphabetic Characters (Excel 2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Your code also strips the non-numeric, non-alphabeticals (#, %, *) that the OP asked to keep...

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove Alphabetic Characters (Excel 2003)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Thanks, that will teach me to assume <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  6. #6
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove Alphabetic Characters (Excel 2003)

    Hi Steve,

    I am hoping that there is a formula that can do this so that it will be great learning experience to see how the formula works.
    Thank for the code, it does removes unwanted characters. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    cheers, francis
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Remove Alphabetic Characters (Excel 2003)

    It's possible to remove the alphabetic part from the beginning or end of a value using an array formula, but to remove A ... Z from anywhere in a value is too complicated. VBA makes it a lot easier.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Remove Alphabetic Characters (Excel 2003)

    AFAIK, it could only be done with multiple intermediate formulas. It could not be done with just one since each can only have 7 nested uses and this would require 26 uses of SUBSTITUTE.

    In B1 you could enter:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),"A",""),"B", ""),"C",""),"D",""),"E",""),"F",""),"G","")

    In C1:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1,"H",""), "I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O ","")

    In D1:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"P",""), "Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W ","")

    And finish in E1 whcih would have the fully stripped version:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1,"X",""),"Y"," "),"Z","")

    Steve

Posting Permissions

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