How to remove alphanumeric numbers from a column

[James Lawson]

Hi everyone,

I'm fairly new to excel so I am hoping that you might be able to help me out.

I have a column of fields with the following pattern:

CONTACT102764309

I wish to delete "CONTACT" from this so that every field in the column shows just the numeric values.
e.g.102764309

How can I do this?

With thanks

James

 

[Trevor G]

James welcome to the Forum,

Just use Find and Replace, and tell it to find Contact but replace with nothing, Highlight the column first

 

[P.Holko]

Try this user-defined function:

Function OnlyNumeric(str) As Double
total = vbNullString
For i = 1 To Len(str)
    If IsNumeric(Mid(str, i, 1)) Then total = total & Mid(str, i, 1)
Next i
    OnlyNumeric = Val(total)
End Function

hope this helps

 

[MickG]

Try:-

[COLOR=navy]Sub[/COLOR] MG31Aug20
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]For[/COLOR] n = 1 To Len(Dn)
        [COLOR=navy]If[/COLOR] Mid(Dn, n, 1) Like "[0-9]" [COLOR=navy]Then[/COLOR] [COLOR=navy]Exit[/COLOR] For
    [COLOR=navy]Next[/COLOR] n
Dn = Right(Dn, Len(Dn) - n + 1)
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick