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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
L

Legacy 456155

Guest
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
 

Ozzy_Bruce

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

Legacy 456155

Guest
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
 

Ozzy_Bruce

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

ADVERTISEMENT

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
 
L

Legacy 456155

Guest
Let me know if you need further explanation. Have a nice weekend. :)
 

Ozzy_Bruce

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

ADVERTISEMENT

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
 

Peter_SSs

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

Legacy 456155

Guest
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.
 

Ozzy_Bruce

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

Watch MrExcel Video

Forum statistics

Threads
1,114,124
Messages
5,546,062
Members
410,726
Latest member
TheSardOz
Top