VBA to extract digits

[Shadkng]

Hi, I have been searching to find a simple code to extract a set of digits from a numeric string. I would normally present some code but I haven't been able to formulate anything that is close to what I want.

My data will be always 12 digits (actually a UPC code). For example, 093427123456. I need to extract the 7th digit thru the 11th digit, so 12345. It would need to run through all the cells of one column, say col "G". The new data can replace the original data in the same cell.

Thanks for the help!

Steve

 

[eduzs]

Maybe something like if 093427123456 is text, not 0,93427123456):
(test in a throw away worksheet, backup before doing any modification)

Sub test()
Dim lRow As Long
With ActiveSheet
    For lRow = 1 To .UsedRange.Rows.Count
        .Cells(lRow, 7) = Mid(ActiveSheet.Cells(lRow, 7), 6, 5)
    Next lRow
End With
End Sub

 

[JLGWhiz]

You can do that with a formula.

=Mid(G2,7,5)

If you need vba then

sub t()
Dim i As Long
With ActiveSheet
    For i = 2 To .Cells(Rows.Count, "G").End(xlUp).Row
        Sheets(2).Cells(i, 1) = .Application.Mid(.Cells(i, 7),7,5)
    Next
End With
End Sub

 

[Shadkng]

You can do that with a formula.

=Mid(G2,7,5)

If you need vba then

sub t()
Dim i As Long
With ActiveSheet
    For i = 2 To .Cells(Rows.Count, "G").End(xlUp).Row
        Sheets(2).Cells(i, 1) = .Application.Mid(.Cells(i, 7),7,5)
    Next
End With
End Sub

Hi Eduzs, your code worked fine. JLGWhiz, I got a debug on your code...could it have something to do with the "Sheets(2)" part? Am I supposed to change that?

Thank you both for your quick replies!

 

[JLGWhiz]

Hi Eduzs, your code worked fine. JLGWhiz, I got a debug on your code...could it have something to do with the "Sheets(2)" part? Am I supposed to change that?

Thank you both for your quick replies!

The sheets(2) is the destination for the results. The error was because there is a period in front of 'Application' that should not be there.

 

[Shadkng]

Maybe something like if 093427123456 is text, not 0,93427123456):
(test in a throw away worksheet, backup before doing any modification)

Sub test()
Dim lRow As Long
With ActiveSheet
    For lRow = 1 To .UsedRange.Rows.Count
        .Cells(lRow, 7) = Mid(ActiveSheet.Cells(lRow, 7), 6, 5)
    Next lRow
End With
End Sub

Hi Eduzs,

I noticed that the "usedrange' is not including the last couple of lines in the range and I'm not sure why. Is there an alternate way to find the last row? Or I can set a range to line 500 which I will never exceed. Thanks again. Steve

 

[eduzs]

Try (last used row column "G"):

For lRow = 1 To .Cells(.Count, "G").End(xlUp).Row

 

[Shadkng]

Try (last used row column "G"):

For lRow = 1 To .Cells(.Count, "G").End(xlUp).Row

Still having trouble. Here is my current code - I had changed it to work on Column A instead of G, and to begin on row 5. Thanks

Sub Trim_UPC()
Dim lRow As Long
With ActiveSheet
    'For lRow = 5 To .UsedRange.Rows.Count  
     For lRow = 1 To .Cells(.Count, "A").End(xlUp).Row
    .Cells(lRow, 1) = Mid(ActiveSheet.Cells(lRow, 1), 7, 5)
    Next lRow
End With
End Sub

 

[Fluff]

How about

Sub Shadkng()
   With Range("A5", Range("A" & Rows.Count).End(xlUp))
      .Value = Evaluate("if({1},mid(" & .Address & ",7,5))")
   End With
End Sub

 

[Shadkng]

Fluff, this works fine. One small glitch which happened on the previous versions as well. On the below sequence it's returning "3201" instead of "03201", I guess because of the "0".
93427103201
Thanks