Flora Edwards
asked on
Remove only Alpha Characters
Hello,
i need help, i have the below code that somehow works through UDF. but what i need is a solution with formula.
that for example in Cell A1 if i have a string like this Edward E%E-(510)-797-2145 @ % then it extracts everything but the alphas and in A2 i need %-(510)-797-2145 @ %
is this possible with excel formula?
i need help, i have the below code that somehow works through UDF. but what i need is a solution with formula.
that for example in Cell A1 if i have a string like this Edward E%E-(510)-797-2145 @ % then it extracts everything but the alphas and in A2 i need %-(510)-797-2145 @ %
is this possible with excel formula?
Function RemoveAlpha(cellInput) As String
Set cellInput = Intersect(cellInput.Parent.UsedRange, cellInput)
For i = 1 To Len(cellInput)
Select Case Mid(cellInput, i, 1)
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "-", "/" ,"@" ,"%"
Charval = Mid(cellInput, i, 1)
Case Else
Charval = ""
End Select
RemoveAlpha = RemoveAlpha & Charval
Next i
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks Patrick. I had faced similar question before. frankly, I did not know that it is not possible by built in formula.
ASKER
Thanks for clarification.
Dear Flora. It is possible to do it witoout UDF, but there is one problem: you can't use cycles in formula, so you should analyze and replace each character in your string. Length of formula is limited, so you are also limited in string length.
Attached is sample for strings less then 30 symbols
Remove-alpha.xlsx
Attached is sample for strings less then 30 symbols
Remove-alpha.xlsx
Or you can try
=SUBSTITUTE(SUBSTITUTE(SUB STITUTE(SU BSTITUTE(S UBSTITUTE( SUBSTITUTE (SUBSTITUT E(SUBSTITU TE(SUBSTIT UTE(SUBSTI TUTE(SUBST ITUTE(SUBS TITUTE(SUB STITUTE(SU BSTITUTE(S UBSTITUTE( SUBSTITUTE (SUBSTITUT E(SUBSTITU TE(SUBSTIT UTE(SUBSTI TUTE(SUBST ITUTE(SUBS TITUTE(SUB STITUTE(SU BSTITUTE(S UBSTITUTE( SUBSTITUTE (SUBSTITUT E(SUBSTITU TE(SUBSTIT UTE(SUBSTI TUTE(SUBST ITUTE(SUBS TITUTE(SUB STITUTE(SU BSTITUTE(S UBSTITUTE( SUBSTITUTE (SUBSTITUT E(SUBSTITU TE(SUBSTIT UTE(SUBSTI TUTE(SUBST ITUTE(SUBS TITUTE(SUB STITUTE(SU BSTITUTE(S UBSTITUTE( SUBSTITUTE (SUBSTITUT E(SUBSTITU TE(SUBSTIT UTE(SUBSTI TUTE(SUBST ITUTE(SUBS TITUTE(A1, "a",""),"b ",""),"c", ""),"d","" ),"e",""), "f",""),"g ",""),"h", ""),"i","" ),"j",""), "k",""),"l ",""),"m", ""),"n","" ),"o",""), "p",""),"q ",""),"r", ""),"s","" ),"t",""), "u",""),"v ",""),"w", ""),"x","" ),"y",""), "z",""),"A ",""),"B", ""),"C","" ),"D",""), "E",""),"F ",""),"G", ""),"H","" ),"I",""), "J",""),"K ",""),"L", ""),"M","" ),"N",""), "O",""),"P ",""),"Q", ""),"R","" ),"S",""), "T",""),"U ",""),"V", ""),"W","" ),"X",""), "Y",""),"Z ","")
for any number of characters
=SUBSTITUTE(SUBSTITUTE(SUB
for any number of characters
Thanks, great idea. You can even simplify it:
=SUBSTITUTE(SUBSTITUTE(SUB STITUTE(SU BSTITUTE(S UBSTITUTE( SUBSTITUTE (SUBSTITUT E(SUBSTITU TE(SUBSTIT UTE(SUBSTI TUTE(SUBST ITUTE(SUBS TITUTE(SUB STITUTE(SU BSTITUTE(S UBSTITUTE( SUBSTITUTE (SUBSTITUT E(SUBSTITU TE(SUBSTIT UTE(SUBSTI TUTE(SUBST ITUTE(SUBS TITUTE(SUB STITUTE(SU BSTITUTE(S UBSTITUTE( SUBSTITUTE (LOWER(A1) ,"a","")," b",""),"c" ,""),"d"," "),"e","") ,"f","")," g",""),"h" ,""),"i"," "),"j","") ,"k","")," l",""),"m" ,""),"n"," "),"o","") ,"p","")," q",""),"r" ,""),"s"," "),"t","") ,"u","")," v",""),"w" ,""),"x"," "),"y","") ,"z","")
=SUBSTITUTE(SUBSTITUTE(SUB
ASKER
thank you very much als315 and Saqib