CODEWITHSUNDEEP
excelvbaexcel-formula
Navneet Singh
Navneet Singh

Reputation: 19

Remove Anything that starts from any digit in excel sheet

I have to remove anything from the cell values that start from any digit(0-9). For example cell A1 have the values: afhd21ffh, 126467, hgdasj12jg, 86589, 0186253 the output should be afhd21ffh, hgdasj12jg.

I have searched alot and tried to find the solution but couldn't get the effective one.

Upvotes: 1

Views: 540

Answers (3)

JvdV
JvdV

Reputation: 75950

A rather complicated formula:

=TEXTJOIN(",",1,FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[not(number(substring(.,1,1)+1))]"))

Result:

enter image description here

"//s[not(number(substring(.,1,1)+1))]" literally takes a substring of all nodes from the 1st position from the left and checks if this is not a number when we add 1 to it. This XPATH 1.0 solution is found here

Note: It's an array formula and needs to be confirmed through CtrlShiftEnter

Upvotes: 2

bosco_yip
bosco_yip

Reputation: 3802

Try to use TEXTJOIN+FILTERXML function of which TEXTJOIN() available in Office 365

In B1, enter array formula (Ctrl+shift+Enter) :

=TEXTJOIN(", ",1,FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b[.*0!=0]"))

enter image description here

Upvotes: 2

Applecore
Applecore

Reputation: 4099

Below is a small VBA proc that takes the contents of cell A1, and removes any values that start with a number, handling the leading space for each value, before outputting it back to the worksheet:

Sub sRemoveNumbers()
    Dim aData() As String
    Dim lngLoop As Long
    Dim strOutput As String
    aData() = Split(Range("A1").Value, ",")
    For lngLoop = LBound(aData) To UBound(aData)
        If IsNumeric(Left(Trim(aData(lngLoop)), 1)) = False Then
            strOutput = strOutput & aData(lngLoop) & ","
        End If
    Next lngLoop
    If Right(strOutput, 1) = "," Then strOutput = Left(strOutput, Len(strOutput) - 1)
    If Left(strOutput, 1) = "," Then strOutput = Mid(strOutput, 2)
    Range("A2") = strOutput
End Sub

Regards,

Upvotes: 2

Related Questions