Extract Numbers to new column

[SamCrome]

Hi,

What I have is a column of mixed text and numbers, this is by clause number and desc. so they look something like this.

Column C

13 – Alcoholic drinks and fermented/brewed products, 15 – Dried foods and ingredients

So I need 13 and 15 put in their own cells in a new column.

Thanks,


In case it helps numbers are 01-18 and yes the zero at the front matters for some reason

[AB33]

How do the numbers 13 and 15 appear on a cell? Do they appear as 1315, or 13 in one cell and 15 in the next cell?

[SamCrome]

Yes they should be in a single column....keeping in mind there can be numbers 01-18 in a cell so they need to come out and be dumped in a column so I can do a count of them.

13
15

Unless I had a macro to search for all occurrences of numbers and count them, this will have to do for now.

Thanks,
Sam

[kirkts]

I haven't done this in years and I forgot....

Cell A1 is populates with the following information 100-51040-360-000. I would like to create four different cells so that the information appears respectively in each cell: Cell B1 100; C1 51040; D1 360; E1 000.

I know this is simple, but it's Friday and my brain has shut down.

Thanks!

[jindon]

try

Sub test()
    Dim r As Range, i As Long
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\d+(\.\d+)?"
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            If .test(r.Value) Then
                For i = 0 To .Execute(r.Value).Count - 1
                    r(, i + 2).NumberFormat = "@"
                    r(, i + 2).Value = .Execute(r.Value)(i)
                Next
            End If
        Next
    End With
End Sub

[SamCrome]

This seems to do the trick from another angle but works for me, thanks so much for your work and effort.

Much appreciated