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

 

[Legacy 456155]

The example in my last post removes all formatting and objects. Have you tried it?

 

[Ozzy_Bruce]

Th

My attempt. Hypothetically, if the clipboard is loaded with data as you have described it, the only thing you should have to do is run sub GetRecord.
See zoo import test 2.xlsm in this folder. Copy your web data and click on the button.
Let me know if the code needs clarification.

Option Explicit

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

Public Sub GetRecord()
    Dim ret(0, 28), i As Integer, GenInfo
 
    Application.ScreenUpdating = False
    Application.Goto Sheets("Dump").Range("A1")
    ActiveSheet.Paste
 
    With ActiveSheet
 
        ret(0, 0) = .[F1]
        ret(0, 1) = .[F2]
        ret(0, 2) = .[F3]
        ret(0, 3) = .[F4]
        ret(0, 4) = .[F5]
        ret(0, 5) = .[F6]
        ret(0, 6) = .[B8]
        ret(0, 7) = .[B11]
        ret(0, 8) = .[F12]
        ret(0, 9) = .[G12]
        ret(0, 10) = .[F13]
        ret(0, 11) = .[G13]
        ret(0, 12) = .[A15]
        ret(0, 13) = .[A16]
        ret(0, 14) = .[A17]
        ret(0, 15) = .[E15]
        ret(0, 16) = .[E16]
        ret(0, 17) = .[E17]
     
        GenInfo = ParseGeneralInfo(.[A18])
     
        For i = 0 To 10
            ret(0, 18 + i) = GenInfo(i)
        Next
     
        .DrawingObjects.Delete
        .Cells.Clear
    End With
 
    With Worksheets("Results").Range("A1").End(xlDown).Offset(1).Resize(, 29)
        .Value = ret
        Application.Goto .Item(1), True
    End With
 
    Application.ScreenUpdating = True
End Sub

Private Function ParseGeneralInfo(Target As Range)
    Dim i As Integer, ret(10) As String, f As String, t As String
 
    t = Target
 
    For i = 0 To 10
        f = Choose(i + 1, "(General) Status:", "Secondary Colour:", "Size:", "Coat:", "Grading:", "Microchip:", "Owner Note:", "Nicnak Labradooldes (Health) DNA Result:", "Hip Score:", "Elbows:", "PennHip:")
        ret(i) = GetFieldValue(t, 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

 

[Ozzy_Bruce]

Yes I have tried ver2 and it looks great. I am still stuck with the pasting of the raw data, I still receive a cannot paste to merged cell.
I have returned the file and added "a" to the name.
Thanks so much for your efforts
Bruce

 

[Ozzy_Bruce]

Paste text if you have that option. Have you tried that?

No I dont have a "Paste as text" option
thanks

 

[Legacy 456155]

You should not have to paste anything. Copy your data to the clipboard from the website and then click the button.

 

[Ozzy_Bruce]

Yes I have tried ver2 and it looks great. I am still stuck with the pasting of the raw data, I still receive a cannot paste to merged cell.
I have returned the file and added "a" to the name.
Thanks so much for your efforts
Bruce

If I paste the coiped data from the website in to A1, I receive an "cannot pate to merged cell error. If I paste the data in to K1 for example its fine then I can copy from K1:Q18 and paste it to A1 it works fine.
I have uploaded a notepad file "zoom import test 2a data pasted to notepad" which shows the data copied from the website then the same data after I have pasted it in to excel K1 (an un-merged cell) then copied again and pasted in to the notepad file.
I hope this sheds so light on the matter.
So far the extraction is working great thanks its just the pasting to sheet1 A1.

Thanks so much guys
Cheers Bruec

 

[Legacy 456155]

There is no need to paste anything. The code does it for you. It takes what is on the clipboard, pastes it in the worksheet named dump, parses the data, assigns it to the next available line, deletes any drawing objects, and then clears the cells, including merges. There should not be anything in worksheet Dump. If there is, clear the cells and delete any objects. Then the only steps you will take from there is:

1. Copy your data from the web page.
2. Click on the button.

 

[Ozzy_Bruce]

There is no need to paste anything. The code does it for you. It takes what is on the clipboard, pastes it in the worksheet named dump, parses the data, assigns it to the next available line, deletes any drawing objects, and then clears the cells, including merges. There should not be anything in worksheet Dump. If there is, clear the cells and delete any objects. Then the only steps you will take from there is:

1. Copy your data from the web page.
2. Click on the button.

Thanks I missed your earlier post. I will give that a crack in the morning
Cheers

 

[Legacy 456155]

Peter, is there a way to adjust your formulas to take into account missing semi-colons by peeking at the next field name? What I mean is, Secondary Colour is null and missing the semi-colon. The formula returns a #VALUE!. I'm wondering if, by taking into account the next field name, "Size:", you can make the formula work for null field values/missing semi-colons. Thanks!

zoo import test 2.xlsm
	A	B	C	D
1	Data	Status:	Secondary Colour:	Size:
2	(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;	Active	#VALUE!	Medium
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TRIM(REPLACE(LEFT($A2,FIND(";",$A2,SEARCH(B$1,$A2))-1),1,SEARCH(B$1,$A2)+LEN(B$1),""))
C2:D2	C2	=TRIM(REPLACE(LEFT($A2,FIND(";",$A2,SEARCH(C1,$A2))-1),1,SEARCH(C1,$A2)+LEN(C1),""))

 

[Ozzy_Bruce]

Th

There is no need to paste anything. The code does it for you. It takes what is on the clipboard, pastes it in the worksheet named dump, parses the data, assigns it to the next available line, deletes any drawing objects, and then clears the cells, including merges. There should not be anything in worksheet Dump. If there is, clear the cells and delete any objects. Then the only steps you will take from there is:

1. Copy your data from the web page.
2. Click on the button.

Thanks I ran it this morning and this error came up