how to remove letters from a cell

**legolas** · 07-12-2011, 04:33 AM

dear all,

i have in column A cells with a mix of letters and numbers together in a form like
letters (seperate) then numbers :

TG 342841,TG 441472-RX001023
MS0012146-ML225126
Civ 126019 Cv 3456006

I am looking for an easy way to have only numbers extracted into B,C and D example:

B1 342841
C1 441472
D1 001023

and so on

I appreciate your usual help

regards

R

**StephenR** · 07-12-2011, 05:52 AM

Here is one way. Format columns as text if you want to preserve leading zeros.

Sub x()

Dim oMatches As Object, i As Long, vOut As Variant, rCell As Range

With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "[0-9]+"
     For Each rCell In Range("A1", Range("A1").End(xlDown))
         Set oMatches = .Execute(rCell)
         ReDim vOut(0 To oMatches.Count - 1)
         For i = 0 To oMatches.Count - 1
               vOut(i) = oMatches(i).Value
         Next i
        rCell.Offset(, 1).Resize(, i) = vOut
     Next rCell
     
End With

End Sub

**legolas** · 07-12-2011, 06:36 AM

Thanks StephenR
I assume this is a module as to be inserted but what formula goes in B

thank you

**StephenR** · 07-12-2011, 07:22 AM

That's right. Assumes your data are in A1 and down. No formulae, just run the code.

**snb** · 07-12-2011, 07:41 AM

or

Sub snb()
  with columns(1)
    For j = 1 To 26
      .Replace Chr(64 + j), " "
      .Replace Chr(95 + j), " "
      If j < 5 Then .Replace Choose(j, ",", ".", "-", "_"), " "
    Next
    [A1:A2000] = [index(trim(A1:A2000),)]
   
    .TextToColumns , , , , False, False, False, True, False
  End With
End Sub