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
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?
Yes, that is my question as I am very new to this code. but I see that it adds a +1 after each entry so I am not sure how to reset that.
It is not constant as I can delete rows above and the script still runs but if I insert rows it throws a 1004 error. (if the next line was 11 and i insert 100 lines the next data is entered on row 12) What I would like to do is insert my currently found records / lines (about 2500) so the when Iclick on the script button it aste the new data at the end. (it does that now but I kind of need to reset its counter to line 2500 or something)
Once i have the existing lines above I can run conditional formatting to highlight duplicate records / lines then i can copy that out sort by name then merge any changed or more recent changes.

I hope that explains where I am heading

Cheers Bruce
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Ozzy_Bruce

New Member
Joined
Oct 16, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
when I copy from the website,then click on the VBA button it pastes that data across a line
then the next time it steps to the next line,then I copy again the click the button and like magic the next line is populated.

So If I insert 50 or 2000 lines before this pasting it kind of buggers this and the step to the next line is confuessed.

Is there a way to start the code after I have inserted the data that I have already captured then I want to wash the new selection against the older stuff.

Does this make any sense or shall I send you my phone number so I can explain it better

cheers
B
 

Ozzy_Bruce

New Member
Joined
Oct 16, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
I'm afraid not. Reset to what?
I have found that if I step down the result sheet about 500 lines then paste Data from before (2500 lines) and set a conditional formating for the dogs name column I can detect duplcates and then I dont import more records from that dog.I hope this amkes sense

Cheers
B
 

Ozzy_Bruce

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

ADVERTISEMENT

I have found that if I step down the result sheet about 500 lines then paste Data from before (2500 lines) and set a conditional formating for the dogs name column I can detect duplcates and then I dont import more records from that dog.I hope this amkes sense

Cheers

I do apologies for my spelling but i do suffer from chronic dyslexia which makes this all the more frustrating.
just to give you an idea what this is like ( i had to copy that line in to google to correct my spelling) . I have pasted this comment 3 times in to google to correcct my spelling etc. and I see its still incorrect. I need to retype most words 3 or 4 times as I thinkfaster then my fingers will work and that really buggers whatever you type.
Its a ***** but Ive found ways around this all my 60 years, your group Mr Excel has helped me so much that I just cannot discribe, again thank you to all that have guided me with this issue.
so these 4 lines takes me about 25 minutes but hey I love the puppies that we breed and the happines we provide to their new owners and they seem to understand.

Thanks guys

Bruce
 

Ozzy_Bruce

New Member
Joined
Oct 16, 2020
Messages
35
Office Version
  1. 365
Platform
  1. Windows
Thanks guys it is all sorted now. There were some rows that had a empty cell in column A and that empty cell would become the next line to be pasted to. I filled in any blank cells and now its working perfectly.

Thats to all for thier help.

Regards
Bruce
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,476
Office Version
  1. 365
Platform
  1. Windows
Glad you got it sorted. Thanks for letting us know.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,475
Messages
5,636,545
Members
416,923
Latest member
jarri

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