VBA to Pick Specific details or split and get the data

[exceluser9]

On Column B I have list of different types of descriptions and on column C , D & E I wanted few details from column B (descriptions) as per below example. So where ever the description starts with intelnumr I need a VBA code to pick below details as per below example. For the ones which does not start intelnumbr it should be blank on column C, D & E.
And on column C, D & E if it is already updated with any value VBA code should skip that line and fill only for blank cells on column C,D & E.


[TABLE="width: 0"]
<tbody>[TR]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32164317 - N TILDA RICE 2.652 SV 0.65 - 052289169 - TILDA BASMATIwir - 201652
[/TD]
[TD]G32164317
[/TD]
[TD]52289169
[/TD]
[TD]201652
[/TD]
[/TR]
</tbody>[/TABLE]

Below are few lines which I have.
When the VBA is executed check for intelnumr on descriptions and update required value on column C,D& E if these columns already have a value inputted it should ignore and go to next line. And this data will increase everyday

[TABLE="width: 0"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[/TR]
[TR]
[TD]1234
[/TD]
[TD]intelnumr ID G32164317 - N TILDA RICE 2.652 SV 0.65 - 052289169 - TILDA BASMATIwir - 201652
[/TD]
[TD]G32164317
[/TD]
[TD]52289169
[/TD]
[TD]201652
[/TD]
[/TR]
[TR]
[TD]1235
[/TD]
[TD]intelnumr ID G32254314 - N1 TILDA RICE 2.652 SV 0.65 - 051098169 - TILDA BASMATI BROWN RICE 500G - 201652
[/TD]
[TD]G32254314
[/TD]
[TD]51098169
[/TD]
[TD]201652
[/TD]
[/TR]
[TR]
[TD]1236
[/TD]
[TD]intelnumr ID G32156396 - N1 TILDA RICE 1.391 SV 0.39 - 081475253 - TILDA PULSES EDAMAME 140G - 201652
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1237
[/TD]
[TD]D# 123348123 Coffee - P17 Gate Fee - Euro Food Brands: TPNB - 53847432 : S Ref GRO_22420_47323 : N
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1238
[/TD]
[TD]D# 123348123 Coffee - P17 Gate Fee - Euro Food Brands: TPNB - 53847432 : S Ref GRO_22420_47323 : N
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1239
[/TD]
[TD]D# 123348123 Coffee - P17 Gate Fee - Euro Food Brands: TPNB - 53847432 : S Ref GRO_22420_47323 : N
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1240
[/TD]
[TD]intelnumr ID G32135372 - N1 BETTY CROCKER COFFEE ICING 2.252 SV 0.25 - 079659776 - BETTY CROCKER COFFEE ICING 400G - 201652
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1241
[/TD]
[TD]intelnumr ID G32154788 - KNORR STOCK CUBES 1.51 SV 0.5 - 051021404 - KNORR FISH STOCK CUBES 8 X 10G - 201652
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1242
[/TD]
[TD]claim PD 20/02/2017 TO 26/02/2017 2591627 SPAM CHOP HAM&PORK 340G
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1243
[/TD]
[TD]claim PD 20/02/2017 TO 26/02/2017 16535720 JRDNS NATURAL MUSLI 1KG
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1244
[/TD]
[TD]21327959 BOUNTY 4 PACK 228G
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1245
[/TD]
[TD]29621961 OEP S N STFF DIN KIT 312
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1246
[/TD]
[TD]intelnumr ID G32174317 - N TILDA RICE 2.652 SV0.65 - 052288169 - TILDA BASMATIwir - 201652
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Please could you help

..

Please show us a good representative variety of what can occur after the "intelnumr ID" and explain what the expected results are for each.

 

[exceluser9]

These are the below set of all the example I have found there will nothing apart from this.
For the below first line there is no 8digit number after G32345965 in the middle then don’t need any out put it should be blank.

And there are few example like – and 8 digit number or there is no – before or after 8 digit number.


[TABLE="width: 0"]
<tbody>[TR]
[TD]Column A
[/TD]
[TD]Column B
[/TD]
[TD]Column C
[/TD]
[TD]Column D
[/TD]
[TD]Column E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]intelnumr ID G32345965 - ETH N1 YUTAKA SOUP 1.58>1.08 SV 0.5 YUTAKA YELLOW MISO SOUP 50G - 201751
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]intelnumr ID G32345965 - ETH N1 YUTAKA SOUP 1.58>1.08 SV 0.5 - 076296758 - YUTAKA YELLOW MISO SOUP 50G - 201751
[/TD]
[TD]G32345965
[/TD]
[TD]76296758
[/TD]
[TD]201751
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]intelnumr ID G32345965 - ETH N1 YUTAKA SOUP 1.58>1.08 SV 0.5 076296748 - YUTAKA YELLOW MISO SOUP 50G - 201752
[/TD]
[TD]G32345965
[/TD]
[TD]76296748
[/TD]
[TD]201752
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]intelnumr ID G32345965 - ETH N1 YUTAKA SOUP 1.58>1.08 SV 0.5 076296768 YUTAKA YELLOW MISO SOUP 50G - 201754
[/TD]
[TD]G32345965
[/TD]
[TD]76296768
[/TD]
[TD]201754
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Try this version

Sub Split_Data_v2()
  Dim c As Range

  Application.ScreenUpdating = False
    For Each c In Range("B1", Range("B" & Rows.Count).End(xlUp))
        If Left(LCase(c.Value), 12) = "intelnumr id" Then
          If c.Resize(, 4).SpecialCells(xlConstants).Cells.Count < 4 Then
            If Right(Left(c.Value, InStrRev(c.Value, " - ", Len(c.Value) - 10) - 1), 9) Like "#########" Then
              c.Offset(, 1).Value = Mid(c.Value, 14, 9)
              c.Offset(, 2).Value = Right(Left(c.Value, InStrRev(c.Value, " - ", Len(c.Value) - 10) - 1), 9)
              c.Offset(, 3).Value = Right(c.Value, 6)
            End If
          End If
        End If
    Next c
  Application.ScreenUpdating = True
End Sub