• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract from Item Name

ranjit1964

New Member
Dear Experts
Please assist to extract qty particulars in specified in item name.

Sheet is attached.
Regards
Ranjit
 

Attachments

  • TAIQ.xlsx
    9 KB · Views: 7
63400

1] "WT PER PIECE" in F3, copied down :

=-LOOKUP(1,-LEFT(MID(D3,FIND("X",D3)+1,99),ROW($1:$9)))

2] "PIECES PER CT" in G3, copied down :

=-LOOKUP(1,-RIGHT(LEFT(D3,FIND("X",D3)-1),ROW($1:$9)))

Regards
Bosco
 
Last edited:
Try this macro

Code:
Option Explicit
Sub Extract_Please()
    Dim n As Long
    Dim rgx As Object
    Dim My_NUm As Object
    Dim ws As Worksheet
    Dim lrd%, I%
   
    Set rgx = CreateObject("VBScript.RegExp")
    Set ws = Worksheets("Sheet1")
    lrd = ws.Cells(Rows.Count, "D").End(3).Row

   ws.Range("F3:G" & lrd).ClearContents
With rgx
        .Global = True
        .MultiLine = True
        .Pattern = "(\d+)*(\d+)"
For I = 3 To lrd
            If .Test(Range("D" & I)) Then
                Set My_NUm = .Execute(Range("D" & I))
                 For n = 0 To My_NUm.Count - 1
                    ws.Range("f" & I).Offset(, n) = My_NUm.Item(n)
                 Next n
            End If
  Next I
End With
    Set rgx = Nothing: Set ws = Nothing
    Set My_NUm = Nothing

End Sub
 

Attachments

  • TAIQ.xlsm
    25.2 KB · Views: 2
Last edited:
Just to round things off, a Power Query solution. I am very much an amateur when it comes to PQ but splitting on the transition to number seems to work, though not quite as sophisticated as the regex above which appears to seek two numbers separated by character (in this case, an "X").
 

Attachments

  • TAIQ (PB).xlsx
    24.3 KB · Views: 4
Try this macro

Code:
Option Explicit
Sub Extract_Please()
    Dim n As Long
    Dim rgx As Object
    Dim My_NUm As Object
    Dim ws As Worksheet
    Dim lrd%, I%
  
    Set rgx = CreateObject("VBScript.RegExp")
    Set ws = Worksheets("Sheet1")
    lrd = ws.Cells(Rows.Count, "D").End(3).Row

   ws.Range("F3:G" & lrd).ClearContents
With rgx
        .Global = True
        .MultiLine = True
        .Pattern = "(\d+)*(\d+)"
For I = 3 To lrd
            If .Test(Range("D" & I)) Then
                Set My_NUm = .Execute(Range("D" & I))
                 For n = 0 To My_NUm.Count - 1
                    ws.Range("f" & I).Offset(, n) = My_NUm.Item(n)
                 Next n
            End If
  Next I
End With
    Set rgx = Nothing: Set ws = Nothing
    Set My_NUm = Nothing

End Sub
Hi
Try this macro

Code:
Option Explicit
Sub Extract_Please()
    Dim n As Long
    Dim rgx As Object
    Dim My_NUm As Object
    Dim ws As Worksheet
    Dim lrd%, I%
  
    Set rgx = CreateObject("VBScript.RegExp")
    Set ws = Worksheets("Sheet1")
    lrd = ws.Cells(Rows.Count, "D").End(3).Row

   ws.Range("F3:G" & lrd).ClearContents
With rgx
        .Global = True
        .MultiLine = True
        .Pattern = "(\d+)*(\d+)"
For I = 3 To lrd
            If .Test(Range("D" & I)) Then
                Set My_NUm = .Execute(Range("D" & I))
                 For n = 0 To My_NUm.Count - 1
                    ws.Range("f" & I).Offset(, n) = My_NUm.Item(n)
                 Next n
            End If
  Next I
End With
    Set rgx = Nothing: Set ws = Nothing
    Set My_NUm = Nothing

End Sub
Hi Salim
Thanks for your solution too. it works well
Regards
Ranjit
 
Dear Bosco,
Thanks for attending to the query.
That was absolutely perfect.
Great work.
Regards
Ranjit
Dear Bosco,
Just to update on application of above formula on wider data noticed that if cell contained two X then #n/a is returned.
Regards
Ranjit
 
Back
Top