VBA to Pick Specific details or split and get the data

E

exceluser9

Active Member
Joined
Jun 27, 2015
Messages
388
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]
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Sort by date Sort by votes
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.
Code: 
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
 
Upvote 0
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

[TABLE="width: 758"]
<colgroup><col></colgroup><tbody>[TR]
[TD]intelnumr ID G32276083 - PGTGKS 3-STEP MGDRGS MEGL 2.79>2 SV 0.79 - 081422190 - PGTGKS MEGL KIT MGDRGS 303G - 201731[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32295723 - N3 ETH FLGVG-IT SEGSONING 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201736[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32227319 - N1 SUN-PGT CRUNCHY 4.15>3 SV 1.15 - 074695193 - SUN-PGT CRUNCHY PEGNUT BUTTER 600G - 201717[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32252490 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 067896991 - FLGVG-IT CHINESE MGRINGDE SEGSONING 45G - 201723[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32217590 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 067897431 - FLGVG-IT PIRI PIRI MGRINGDE SEGSONING 45G - 201715[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201707[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201705[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201708[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32230373 - N1 ETH MGLT-O-MEGL CEREGL 3>2 SV 1 - 076235787 - MGLT-O-MEGL M/MGLLOW MGTEYS CEREGL 320G - 201718[/TD]
[/TR]
[TR]
[TD]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[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32232358 - N1 ETH NO-NO FLGTBREGD 1.65>1.24 SV 0.41 - 066021030 - NO-NO SESGME FLGTBREGD 130G - 201726[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32330255 - NUTRI-BREX GLUTEN FREE 2.99>2.5 SV 0.49 - 081607029 - NUTRIBREX COCONUT & CRISPY RICE 375G - 201744[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32290539 - ETH N1 MGLT-O-MEGL MGRSHMGLLOW 3>2 SV 1 - 080222318 - MGLT-O-MEGL M/MGLLOW MGTEYS CHOC FLGV 340G - 201732[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32230373 - N1 ETH MGLT-O-MEGL CEREGL 3>2 SV 1 - 076235787 - MGLT-O-MEGL M/MGLLOW MGTEYS CEREGL 320G - 201716[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32249216 - N1 FGRGBELLG GLUTEN-FREE 2.99>2.49 SV 0.5 - 081674032 - GLUTEN-FREE FRESH GNOCCI GNOCCI - 201723[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32290538 - ETH N1 MGLT-O-MEGL MGRSHMGLLOW 3>2 SV 1 - 076235787 - MGLT-O-MEGL M/MGLLOW MGTEYS CEREGL 320G - 201734[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32230373 - N1 ETH MGLT-O-MEGL CEREGL 3>2 SV 1 - 076235787 - MGLT-O-MEGL M/MGLLOW MGTEYS CEREGL 320G - 201719[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32227362 - N1 WHITWORTHS CGCGO MGCG-ROONS 1.5>1 SV 0.5 - 082323651 - WHITWORTHS CGCGO MGCG-ROONS 2 PGCK 50G - 201721[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32250429 - N1 FGRGBELLG GLUTEN-FREE 2.99>2.49 SV 0.5 - 081674032 - GLUTEN-FREE FRESH GNOCCI GNOCCI - 201730[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32295735 - N3 ETH FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 077786386 - FLGVG-IT GHOST CHILLI MGRINGDE 45G - 201736[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201706[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32252490 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201723[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32273609 - N1 WHITWORTHS MGCG-ROONS 1.5>1 SV 0.5 - 082323645 - WHITWORTHS VGNILLG MGCGROON 2 PGCK 50G - 201731[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32173190 - N1 FLGVG -IT MGRINGDE 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201705[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32217590 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 067897431 - FLGVG-IT PIRI PIRI MGRINGDE SEGSONING 45G - 201716[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32217590 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201717[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32295723 - N3 ETH FLGVG-IT SEGSONING 0.99>0.75 SV 0.24 - 067896985 - FLGVG-IT BBQ MGRINGDE SEGSONING 45G - 201735[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32295725 - N3 ETH FLGVG-IT SEGSONING 0.99>0.75 SV 0.24 - 067897431 - FLGVG-IT PIRI PIRI MGRINGDE SEGSONING 45G - 201737[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32217590 - N1 ETH FLGVG-IT SSNG 0.99>0.75 SV 0.24 - 081503820 - FLGVG-IT SWEET CHILLI MGRINGDE 45G - 201716[/TD]
[/TR]
[TR]
[TD]intelnumr ID G32295725 - N3 ETH FLGVG-IT SEGSONING 0.99>0.75 SV 0.24 - 067897431 - FLGVG-IT PIRI PIRI MGRINGDE SEGSONING 45G - 201736[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try:
Code: 
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
 
Upvote 0
Another slight variation to try
Code: 
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
 
Upvote 0
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
[TABLE="width: 695"]
<tbody>[TR]
[TD="class: xl63, width: 695"]intelnumr ID G32312671 - ETH K.A. SPARKLING DRK 2 for £2 053031867 - KA SPARKLING PINEAPPLE 2 LTR BOTTLE - 201743[/TD]
[/TR]
</tbody>[/TABLE]

please help final one i think
 
Upvote 0
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)?
 
Last edited:
Upvote 0
exceluser9 said:
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
Click to expand...

Try to change the mumps routine splitData() in post #2.
Replace:
Code: 
splitRng = Split(rng, "-")

with:
Code: 
splitRng = Split(rng, " - ")


If you have more problems, then should be good if public a sample file with complete lists of strings.
 
Last edited:
Upvote 0
gibra said:
Replace:
Code: 
splitRng = Split(rng, "-")

with:
Code: 
splitRng = Split(rng, " - ")
Click to expand...
Perhaps you missed post #6 - some of the data has no "-" at all before the number to be extracted, :)
exceluser9 said:
if the number does not start with hyphen - macro stops ..... in the below description there is no hyphen before the number 53031867
[TABLE="width: 695"]
<tbody>[TR]
[TD="class: xl63, width: 695"]intelnumr ID G32312671 - ETH K.A. SPARKLING DRK 2 for £2 053031867 - KA SPARKLING PINEAPPLE 2 LTR BOTTLE - 201743[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,909
Messages
6,175,315
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top