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

Ozzy_Bruce

New Member
Joined
Oct 16, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 16, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 16, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
OK, great. Glad you have something that works for you. (y)
 

Ozzy_Bruce

New Member
Joined
Oct 16, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

Ozzy_Bruce

New Member
Joined
Oct 16, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,128
Messages
5,628,864
Members
416,346
Latest member
Sekolaine

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
Top