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>

 

[Peter_SSs]

You didn't fully answer..

1. ... can you guarantee that there isn't more than one 9-digit number in the original cell?


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

 

[exceluser9]

Hi Sir,

1. ... can you guarantee that there isn't more than one 9-digit number in the original cell? - yes confirm it will always be 9 digit number on original cell


3. Is the number to go into column E always the last thing in the cell? ............... (& the only6 digit number in the cell)? - on column E it will be ending with last 6 digit number

 

[Peter_SSs]

- yes confirm it will always be 9 digit number on original cell

That seems to be still telling me that it will be a 9-digit number. It doesn't seem to be telling me if there could be other 9-digit numbers in the same cell. Like this, for example

intelnumr ID G32164317 - AB ver 569865489 ex 2.652 SV 0.65 - 052289169 - TILDA BASMATIwir - 201652

 

[gibra]

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

Yes, I missed.

This should cover this case, also:

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)
            If Not IsNumeric(splitRng(2)) Then
                Range("D" & rng.Row) = Right$(splitRng(1), 9)
            Else
                Range("D" & rng.Row) = splitRng(2)
            End If
            Range("E" & rng.Row) = Right(rng, 6)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

However, exceluser9 should be public a file with all cases.

 

[exceluser9]

Hi,

In the same cell there is only one 9 digit number like below. there wil not be another 9 digit number like you mentioned above.
intelnumr ID G32164317 - AB ver 569865489 ex 2.652 SV 0.65 - TILDA BASMATIwir - 201652

 

[exceluser9]

Hi Gibra,

the below code work fine with out any issue thank you very much

Thank Every one who helped me on this

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)
If Not IsNumeric(splitRng(2)) Then
Range("D" & rng.Row) = Right$(splitRng(1), 9)
Else
Range("D" & rng.Row) = splitRng(2)
End If
Range("E" & rng.Row) = Right(rng, 6)
End If
Next rng
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

For the record, or in case you are interested, this would be my variation, avoiding splits altogether.

Sub Split_Data()
  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
            c.Offset(, 1).Value = Mid(c.Value, 14, 9)
            c.Offset(, 2).Value = Right(Left(c.Value, InStrRev(c.Value, " - ", Len(c.Value) - 10)), 9)
            c.Offset(, 3).Value = Right(c.Value, 6)
          End If
        End If
    Next c
  Application.ScreenUpdating = True
End Sub

Again, in case you are interested, all the red lines below in gibra's code I believe could be replaced by this single line

Range("D" & rng.Row) = Right$(splitRng(UBound(splitRng) - 2), 9)

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)
           [COLOR="#FF0000"][B] If Not IsNumeric(splitRng(2)) Then
                Range("D" & rng.Row) = Right$(splitRng(1), 9)
            Else
                Range("D" & rng.Row) = splitRng(2)
            End If[/B][/COLOR]
            Range("E" & rng.Row) = Right(rng, 6)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[exceluser9]

Hi Peter,

Thank you very much, actually i got stuck again with Gibra 's code which is if there is no 8 digit number on the main description the macro stops but the code which you gave it work fine but if there is not 8 digit number in middle of descriptions it pick the 8 digit number from beinging example below. it pick the first one 32276083.

can it not be blank if there is no 8digit number in the middle ?


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

 

[Peter_SSs]

... if there is no 8 digit number on the main description ....

Hmm, do you remember me posting..

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.

So here we are, after a day and a half and nearly 20 posts, including nearly 40 examples of text starting with "intelnumr ID", all of which were followed by the letter "G" and then 8 digits. Only now we learn that other things can happen.

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]

Hi Peter
Thanks so much for your help and sry if I'm troubling.

Your code looks fine and it works as expected but only challenge is if 9 digit number isn't available in the cell it picks G32276083 which has the number which has a alphabet and it will start with G and it should remain blank in this case. There can be only one 9 digit number in a cell.

Please could you help