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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Can't help you with built-in functions. Here is a custom function that returns an array of parsed items.
Example:
Data in A1
Select B1 to L1.
Enter =ParseData(A1) in the formula bar.
Press CTRL-SHIFT-ENTER to enter the array formula.
You can drag/fill B1 to L1 down to parse other items in column A

In a standard module:
VBA Code:
Function ParseData(DataIn As String)
    Dim s() As String, i As Integer, ret(10) As String
   
    s = Split(DataIn, ":")
   
    For i = 1 To UBound(s)
        ret(i - 1) = Split(s(i), ";")(0)
    Next
   
    ParseData = ret
End Function
 
Upvote 0
Thanks for your offer of help.
There will only be 1 cell that needs to be split.
can I do some kind of Find : and stop at ; and then do a repetition of that to the next : and stop at the ; and so on?
 
Upvote 0
The raw data is in one cell in my example. A1, for example. It will be split into 11 columns. B1 to L1.
You may download an example in this folder.
See: Dog Breeder Parse.xlsm
 
Upvote 0
The raw data is in one cell in my example. A1, for example. It will be split into 11 columns. B1 to L1.
You may download an example in this folder.
See: Dog Breeder Parse.xlsm
Thank you. looks great but I cannot see how it was done.
But I can atleast google from there

Cheers
Bruce
 
Upvote 0
Let me know if you need further explanation. Have a nice weekend. :)
I have managed to complete that same as you have for me, the only down side is that each time i change the raw data I need to rebuild the txt to column? ia that correct?.
I am try to just paste my copies data to sheet one then have it split out in to columns on sheet two. Is there a refresh what would update the output.

Thanks Bruce
 
Upvote 0
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 was wondering about the detail of the "and so on" given that in the central section of your sample there are two colons with no semicolon between.

.. 99; Owner Note: Nicnak Labradooldes (Health) DNA Result: Clear; Hip ..

So here are you expecting
Clear
or
Nicnak Labradooldes (Health) DNA Result: Clear
or
Nicnak Labradooldes (Health) DNA Result
or
something else?

.. Or was the sample data incorrect?
 
Upvote 0
the only down side is that each time i change the raw data I need to rebuild the txt to column?
Not necessary. The formulas' output will update with new data. You can move the formulas wherever you wish such as a different worksheet. If you need help setting up your workbook, upload a copy with an example of the raw data contained therein. You are welcome to upload to the folder/link I posted earlier. If the website is publicly accessible, post a link to the data.
 
Upvote 0
Thank you again
Owner Note: Nicnak Labradooldes (Health) DNA Result: Clear; Hip .. should have an semi colon after the "Health)"but it was not there whan I copied the raw so I will just add it as required.
(Health) ; DNA Result: Clear; Hip
And so on, I meant to keep inserting the data in to columns until the end.

So to summerise I am trying to copy data from our clubs database and paste it in to excel so I can prepare it to import in to my own database. just trying not to have to type everything.

I will send you a workbook where the first sheet is the raw data pasted the second sheet is workings and the thrid I would love it if I can somehow add the data to a new row automaticly but Im happy to cut and the whole line and paste as values.

I really appreaciate your help and I am enjoying learning more about excel.

I have named it Zoo Import Test

Thanks Bruce
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,728
Members
448,294
Latest member
jmjmjmjmjmjm

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
Back
Top