Stuck again. extract data from cell between : &; mulitiple instances

[Ozzy_Bruce]

Hi guys, this has me stumped.
I am copying data from an online database that I am wanting to add to my own db, I can copy from the info from the online database and paste in to excel, all good. I have the next sheet pull the data from that paste with a simlpe =, but one of the copied cells includes a heap of data that I want to split in to new columns eg

(General) Status: Active; Secondary Colour: ; Size: Medium; Coat: Fleece; Grading: AL; Microchip: 953010001993799; Owner Note: Nicnak Labradooldes (Health) DNA Result: Clear; Hip Score: 5+7=12; Elbows: 0+0; PennHip: R .41 L .47;

Yes I am a dog breeder

And I would like to capture the data between the : & ; so the first instance would be "Active" then " " then "Fleece" then "AL" and so on. I have googled this to death but only find help on the first instance or data between spaces Etc.
Id love to put the formula in each cell to pull each result.

I am sure that I am going about this the hard way. Copying records from the online Db to Excel to then import in to my own Db. but its a lot faster then copy and paste each field.

Thanks for any help
Cheers Newby

Bruce

 

[Ozzy_Bruce]

Could the code you have be adjusted to remove all formatting from that sheet 1 range once it has copied the data to the results sheet?

The code I have does just that but I am not sure how to implement that code with your formula

To include AVK, replace the code in module1 with the code below. The example download includes any updates we have made. I'd still like to try the formula approach to learn and perhaps reduce the amount of code as well.

Module1:

Option Explicit

Sub TestLoadClipBoard()
    Sheets("Test").Range("A1:H18").Copy
End Sub

Public Sub GetRecord()
    Dim ret(0, 29), i As Integer, GenInfo, GenInfoText As String
   
    Application.ScreenUpdating = False
    Application.Goto Sheets("Dump").Range("A1")
    ActiveSheet.Paste
   
    With ActiveSheet

        ret(0, 0) = .[F1] 'PedigreeNo:
        ret(0, 1) = .[F2] 'Gender:
        ret(0, 2) = .[F3] 'Name:
        ret(0, 3) = .[F4] 'Given name:
        ret(0, 4) = .[F5] 'Breed:
        ret(0, 5) = .[F6] 'Colour:
        ret(0, 6) = .[B8] 'Born:
        ret(0, 7) = .[B11] 'Breed percentage:
        ret(0, 8) = .[F12] 'Father Number:
        ret(0, 9) = .[G12] 'Father Name:
        ret(0, 10) = .[F13] 'Mother Number:
        ret(0, 11) = .[G13] 'Mother Name:
        ret(0, 12) = .[A15] 'Breeder Number:
        ret(0, 13) = .[A16] 'Breeder Name:
        ret(0, 14) = .[A17] 'Breeder Kennel:
        ret(0, 15) = .[E15] 'Owner Number:
        ret(0, 16) = .[E16] 'Owner Name:
        ret(0, 17) = .[E17] 'Owner Kennel:
        ret(0, 18) = .[F9] 'AKV:
       
        GenInfoText = .[A18]
       
        .DrawingObjects.Delete
        .Cells.Clear
       
        GenInfo = ParseGeneralInfo(GenInfoText)
       
        For i = 0 To 10
            ret(0, 19 + i) = GenInfo(i)
        Next
       
    End With
   
    With Worksheets("Results").Range("A1").End(xlDown).Offset(1).Resize(, 30)
        .Value = ret
        Application.Goto .Item(1), True
    End With
   
    Application.ScreenUpdating = True
End Sub

Private Function ParseGeneralInfo(GenInfoText As String)
    Dim i As Integer, ret(10) As String, f As String, t As String
   
    For i = 0 To 10
        f = Choose(i + 1, "Status:", "Secondary Colour:", "Size:", "Coat:", "Grading:", "Microchip:", "Owner Note:", "DNA Result:", "Hip Score:", "Elbows:", "PennHip:")
        ret(i) = GetFieldValue(GenInfoText, f)
    Next
  
    ParseGeneralInfo = ret
End Function

Private Function GetFieldValue(t As String, f As String) As String
    Dim s As String, i As Integer, c As String
    s = Split(t, f)(1)
    For i = 1 To Len(s)
        c = Mid(s, i, 1)
        If c = ";" Then
            GetFieldValue = Trim(Left(s, i - 1))
            Exit Function
        ElseIf c = ":" Then
            Exit Function
        End If
    Next
End Function

Sorry I did not ctah on when you said that you had updated the code.
I have run this and it appears to work sopt on. I had forgotton to add the extra colunm for the AKV for they populated 1 to the left until I remembered.
I am hoping that I will have more time this afternoon to test it more but its looking like it does the trick.
Thanks all for your help todate.
Bruce.

 

[Peter_SSs]

The code I have does just that but I am not sure how to implement that code with your formula

So, could you post (here in the thread) the code that you have that is doing that successfully?

BTW, the other poster who was helping in this thread is no longer a member of the forum.

 

[Ozzy_Bruce]

Could the code you have be adjusted to remove all formatting from that sheet 1 range once it has copied the data to the results sheet?

Post 46 from dataluver seems to have it sorted using your formula I think.
I just copy from the website and click the button on the 2nd sheet.

 

[Peter_SSs]

OK, great. Glad you have something that works for you.

 

[Ozzy_Bruce]

OK, great. Glad you have something that works for you.

I will try and run some data through it tonight, then I will mark it as resolved.

I might have to hang around this board and see what other tricks I can pick up.

thanks for all the help. I’ll by you a beer when your in town

cheers
Bruce

 

[Peter_SSs]

I’ll by you a beer when your in town

Which town?

 

[Ozzy_Bruce]

Which town?

Gold Coast. Not too far from you really

 

[Peter_SSs]

Gold Coast. Not too far from you really

No, not far but I'm still not allowed to cross the border!

 

[Ozzy_Bruce]

No, not far but I'm still not allowed to cross the border!

Peter I would like to copy / post some lines in to the result sheet above the current line that the script is posting to. when I do this I get a 1004 error. If I can insert a few hundred lines I can check for duplicate records as I go.
Is there a way I can tell the VBA which line to start posting its results to? Could you or one of the other masters of excel have a look at the code in post 46 for me

Thanks Bruec.

 

[Peter_SSs]

have a look at the code in post 46 for me

Do you mean the code in post #45?

Which line in the code needs to post to a particular row & how will that row be determined?