Remove Alphabetic Characters (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

Your code also strips the non-numeric, non-alphabeticals (#, %, *) that the OP asked to keep…

Steve

Reply | Quote

Thanks, that will teach me to assume

Reply | Quote

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 LegareColeman‘s Personal.xls Tutorial (All) for more info on using Personal.xls)

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)  90 Then '65-90 are ASCII "A"-"Z"
      'sCharacter is NOT a LETTER
      sTemp = sTemp & sCharacter
    End If
  Next x
  StripAlphabet = sTemp
End Function

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

Reply | Quote

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.

cheers, francis

Reply | Quote

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.

Reply | Quote

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(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1),”A”,””),”B”,””),”C”,””),”D”,””),”E”,””),”F”,””),”G”,””)

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

In D1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(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

Reply | Quote