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.

Column B	Column C	Column D	Column E
intelnumr ID G32164317 - N TILDA RICE 2.652 SV 0.65 - 052289169 - TILDA BASMATIwir - 201652	G32164317	52289169	201652

<tbody>
</tbody>

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

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

<tbody>
</tbody>

 

[mumps]

This macro assumes that the extracted values are always in the same place in the string in column B and always of the same length.

Sub splitData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim splitRng As Variant
    For Each rng In Range("B1:B" & LastRow)
        If Left(rng, 12) = "intelnumr ID" And WorksheetFunction.CountA(Range("C" & rng.Row & ":E" & rng.Row)) <> 3 Then
            splitRng = Split(rng, "-")
            Range("C" & rng.Row) = Mid(rng, 14, 9)
            Range("D" & rng.Row) = splitRng(2)
            Range("E" & rng.Row) = Right(rng, 6)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[exceluser9]

Hi Thank you very much for your help . it is working fine but for few description below it does not pull the middle part which wil be on column D.

These are the below description for which it did not pull the middle part, on the below first line it did not pick -81422190

intelnumr ID G32276083 - PGTGKS 3-STEP MGDRGS MEGL 2.79>2 SV 0.79 - 081422190 - PGTGKS MEGL KIT MGDRGS 303G - 201731

intelnumr ID G32295723 - N3 ETH FLGVG-IT SEGSONING 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201736

intelnumr ID G32227319 - N1 SUN-PGT CRUNCHY 4.15>3 SV 1.15 - 074695193 - SUN-PGT CRUNCHY PEGNUT BUTTER 600G - 201717

intelnumr ID G32252490 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 067896991 - FLGVG-IT CHINESE MGRINGDE SEGSONING 45G - 201723

intelnumr ID G32217590 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 067897431 - FLGVG-IT PIRI PIRI MGRINGDE SEGSONING 45G - 201715

intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201707

intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201705

intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201708

intelnumr ID G32230373 - N1 ETH MGLT-O-MEGL CEREGL 3>2 SV 1 - 076235787 - MGLT-O-MEGL M/MGLLOW MGTEYS CEREGL 320G - 201718

intelnumr ID G32246054 - BE-RO LIGHT PLGIN FLOUR 1.42>0.75 SV 0.67 - 050039725 - BE-RO PLGIN FLOUR 1.5KG 20% X/FREE - 201724

intelnumr ID G32232358 - N1 ETH NO-NO FLGTBREGD 1.65>1.24 SV 0.41 - 066021030 - NO-NO SESGME FLGTBREGD 130G - 201726

intelnumr ID G32330255 - NUTRI-BREX GLUTEN FREE 2.99>2.5 SV 0.49 - 081607029 - NUTRIBREX COCONUT & CRISPY RICE 375G - 201744

intelnumr ID G32290539 - ETH N1 MGLT-O-MEGL MGRSHMGLLOW 3>2 SV 1 - 080222318 - MGLT-O-MEGL M/MGLLOW MGTEYS CHOC FLGV 340G - 201732

intelnumr ID G32230373 - N1 ETH MGLT-O-MEGL CEREGL 3>2 SV 1 - 076235787 - MGLT-O-MEGL M/MGLLOW MGTEYS CEREGL 320G - 201716

intelnumr ID G32249216 - N1 FGRGBELLG GLUTEN-FREE 2.99>2.49 SV 0.5 - 081674032 - GLUTEN-FREE FRESH GNOCCI GNOCCI - 201723

intelnumr ID G32290538 - ETH N1 MGLT-O-MEGL MGRSHMGLLOW 3>2 SV 1 - 076235787 - MGLT-O-MEGL M/MGLLOW MGTEYS CEREGL 320G - 201734

intelnumr ID G32230373 - N1 ETH MGLT-O-MEGL CEREGL 3>2 SV 1 - 076235787 - MGLT-O-MEGL M/MGLLOW MGTEYS CEREGL 320G - 201719

intelnumr ID G32227362 - N1 WHITWORTHS CGCGO MGCG-ROONS 1.5>1 SV 0.5 - 082323651 - WHITWORTHS CGCGO MGCG-ROONS 2 PGCK 50G - 201721

intelnumr ID G32250429 - N1 FGRGBELLG GLUTEN-FREE 2.99>2.49 SV 0.5 - 081674032 - GLUTEN-FREE FRESH GNOCCI GNOCCI - 201730

intelnumr ID G32295735 - N3 ETH FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 077786386 - FLGVG-IT GHOST CHILLI MGRINGDE 45G - 201736

intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201706

intelnumr ID G32252490 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201723

intelnumr ID G32273609 - N1 WHITWORTHS MGCG-ROONS 1.5>1 SV 0.5 - 082323645 - WHITWORTHS VGNILLG MGCGROON 2 PGCK 50G - 201731

intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201705

intelnumr ID G32217590 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 067897431 - FLGVG-IT PIRI PIRI MGRINGDE SEGSONING 45G - 201716

intelnumr ID G32217590 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201717

intelnumr ID G32295723 - N3 ETH FLGVG-IT SEGSONING 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201735

intelnumr ID G32295725 - N3 ETH FLGVG-IT SEGSONING 0.99>0.75 SV 0.24 - 067897431 - FLGVG-IT PIRI PIRI MGRINGDE SEGSONING 45G - 201737

intelnumr ID G32217590 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201716

intelnumr ID G32295725 - N3 ETH FLGVG-IT SEGSONING 0.99>0.75 SV 0.24 - 067897431 - FLGVG-IT PIRI PIRI MGRINGDE SEGSONING 45G - 201736

<colgroup><col></colgroup><tbody>
</tbody>

 

[mumps]

Try:

Sub splitData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("B1:B" & LastRow)
        If Left(rng, 12) = "intelnumr ID" And WorksheetFunction.CountA(Range("C" & rng.Row & ":E" & rng.Row)) <> 3 Then
            Range("C" & rng.Row) = Mid(rng, 14, 9)
            Range("D" & rng.Row) = Mid(Range("B" & rng.Row), WorksheetFunction.Search("- ????????? -", Range("B" & rng.Row), 1) + 2, 9)
            Range("E" & rng.Row) = Right(rng, 6)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

Another slight variation to try

Sub intelnumr()
  Dim Bits As Variant
  Dim c As Range
  
  Application.ScreenUpdating = False
  For Each c In Range("B2", Range("B" & Rows.Count).End(xlUp))
    With c
      If Left(LCase(.Value), 12) = "intelnumr id" Then
        If .Resize(, 4).SpecialCells(xlConstants).Cells.Count < 4 Then
          Bits = Split(.Value, " - ")
          .Offset(, 1).Value = Mid(Bits(0), 14)
          .Offset(, 2).Value = Bits(2)
          .Offset(, 3).Value = Bits(4)
        End If
      End If
    End With
  Next c
  Application.ScreenUpdating = True
End Sub

 

[exceluser9]

HI Thank you for you reply, it is working but again one more issue. if the number does not start with hyphen - macro stops or even after hyphen - if there is double space it does not work. in the below description there is no hyphen before the number 53031867

intelnumr ID G32312671 - ETH K.A. SPARKLING DRK 2 for £2 053031867 - KA SPARKLING PINEAPPLE 2 LTR BOTTLE - 201743

<tbody>
</tbody>

please help final one i think

 

[Peter_SSs]

1. So how do we identify the number for column D? For example, is it always 9 digits and if so can you guarantee that there isn't more than one 9-digit number in the original cell?

2. Are there any other variations with the data in relation to that column D number? For example, might it not even have a space in front of it sometimes? We really need to know all the variations if a suitable solution is to be found without wasting a lot of time developing suggestions that later turn out to be no good.

3. Is the number to go into column E always the last thing in the cell? Alternatively is it always 6 digits (& the only 6 digit number in the cell)?

 

[gibra]

Hi Thank you very much for your help . it is working fine but for few description below it does not pull the middle part which wil be on column D.

These are the below description for which it did not pull the middle part, on the below first line it did not pick -81422190

Try to change the mumps routine splitData() in post #2.
Replace:

splitRng = Split(rng, "-")

with:

splitRng = Split(rng, " - ")

If you have more problems, then should be good if public a sample file with complete lists of strings.

 

[Peter_SSs]

Replace:

splitRng = Split(rng, "-")

with:

splitRng = Split(rng, " - ")

Perhaps you missed post #6 - some of the data has no "-" at all before the number to be extracted,

if the number does not start with hyphen - macro stops ..... in the below description there is no hyphen before the number 53031867

intelnumr ID G32312671 - ETH K.A. SPARKLING DRK 2 for £2 053031867 - KA SPARKLING PINEAPPLE 2 LTR BOTTLE - 201743

<tbody>
</tbody>

 

[exceluser9]

Hi,
Please see my answer below

1. So how do we identify the number for column D? For example, is it always 9 digits and if so can you guarantee that there isn't more than one 9-digit number in the original cell? yes this will always be 9 digits
2. Are there any other variations with the data in relation to that column D number? For example, might it not even have a space in front of it sometimes? We really need to know all the variations if a suitable solution is to be found without wasting a lot of time developing suggestions that later turn out to be no good. :wink:, - there no other variations with data in relation to column D

3. Is the number to go into column E always the last thing in the cell? Alternatively is it always 6 digits (& the only 6 digit number in the cell)? - This will always remain 6 digit