What Code Or Macro Can I Use To Substitute Text To Column

[Corried]

Hello Everyone.

Presently I have Many Raw Data below to section out.

What's beautiful, all the following data are similar:
Here is an example in row "1", in column "A" only :

0: {c: 9, close: 9, yield: null, referenceDate: null, date: "1997-03-01T00:00:00", …}

In this Case: How can I Keep the values of The following:

9	1997-03-01

I have multiple data similar like above. The comparison with the others are, values and dates.

What is the effective way to get the data?
Or what code can I use? How do you want me to proceed?

Thanks in advance

 

[ajetrumpet]

your data looks that that which would come out of a database dump from a mysql db or sql server db. is that what it is?

You can use vba for sure, but you might have better luck if you have any familiarity with the function called REGEX(). ever used that? it's quite complex and you've got to be a pretty good programmer to create an input for it, but if you manage to do it, it can be quite powerful.

 

[Corried]

your data looks that that which would come out of a database dump from a mysql db or sql server db. is that what it is?

You can use vba for sure, but you might have better luck if you have any familiarity with the function called REGEX(). ever used that? it's quite complex and you've got to be a pretty good programmer to create an input for it, but if you manage to do it, it can be quite powerful.

The data is from a web page in insect elements, I understand that programmer is the one who can obtain the data online.

How can you help me with the basic of coding in excell

 

[ajetrumpet]

well before I, or anyone else attempts that, can you post like, say, 10 records from the data dump you've got? that would show whether or not there's a common pattern with the data you're looking at.

and by the way, you don't have to know anything about programming to get data from a webpage into excel. excel has a feature that is able to query, for instance, an HTML table element directly and pull the data out, using only interface menus. so excel is quite useful in this regard.

 

[Corried]

well before I, or anyone else attempts that, can you post like, say, 10 records from the data dump you've got? that would show whether or not there's a common pattern with the data you're looking at.

and by the way, you don't have to know anything about programming to get data from a webpage into excel. excel has a feature that is able to query, for instance, an HTML table element directly and pull the data out, using only interface menus. so excel is quite useful in this regard.

Please view the sample file below.

Please note: the raw data is on cell "A1" and
The extracted data example is on cell "N" and cell "O"

There are many excel workbook I need to extract data.

How can I create a universal code to extract each one.

Thanks in advance.

Book3.xlsx

drive.google.com

 

[ajetrumpet]

ok, I tried to do this in the cells, but excel does not have some of these vba functions built in, so do this:

Function extract_data_from_raw_dump()
Dim r As Range
Dim curRow As Long
Dim col1 As String
Dim col2 As String

On Error GoTo NextR

Application.ScreenUpdating = False
curRow = 1
    For Each r In Range("a1", Range("a1").End(xlDown))
        Range("m" & CStr(curRow)) = CLng(Mid(r, InStr(r, ",") - 2, 2))
        Range("n" & CStr(curRow)) = Mid(r, InStr(r, """") + 1, 10)
NextR:
    curRow = curRow + 1
    Next r
Application.ScreenUpdating = True
End Function

you will have to get rid of the cells that have section indicators in them, like this: [100 … 199] , otherwise it will throw an error. have fun.

 

[Fluff]

With formulae
=LEFT(REPLACE(A1,1,SEARCH("date:"&CHAR(160)&"""",A1)+6,""),10)*1
and
=LEFT(SUBSTITUTE(REPLACE(A1,1,SEARCH("{c:",A1)+3,""),",",REPT(" ",5)),4)*1

 

[ajetrumpet]

see? my complex nature NEVER works best.

 

[Jon von der Heyden]

More formula alternatives.

For c:
=LOOKUP(999999,--MID(A1,FIND("c:",A1)+LEN("c:"),{1;2;3;4;6}))

For date (format as YYYY-MM-DD):
=LOOKUP(99999,--MID(A1,FIND("date: """,A1)+LEN("date: """),{1;2;3;4;6;7;8;9;10}))

 

[Fluff]

@Jon von der Heyden
Unfortunately the spaces are not normal spaces, but CHAR(160).