Solved

Remove only Alpha Characters

Posted on 2015-01-09
7
127 Views
Last Modified: 2015-01-12
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?

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

Open in new window

0
Comment
Question by:Flora
7 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 40540837
I assume what you mean is, "I need something that works using only built-in Excel functions."

In which the answer is no, you need to stick with a UDF, which you can of course refer to in a formula.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40540944
Thanks Patrick. I had faced similar question before. frankly, I did not know that it is not possible by built in formula.
0
 
LVL 5

Author Closing Comment

by:Flora
ID: 40540953
Thanks for clarification.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 39

Expert Comment

by:als315
ID: 40541061
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
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40541191
Or you can try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(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
0
 
LVL 39

Expert Comment

by:als315
ID: 40541709
Thanks, great idea. You can even simplify it:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(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","")
0
 
LVL 5

Author Comment

by:Flora
ID: 40544552
thank you very much als315 and Saqib
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now