Extract data between quotes

[Ed Harris]

Hi
I have this data in a json file which I have pasted into excell and want to extract just the marker number for example 453 and the RA designation in the last row of each block eg 18h07m57.2 to adjacent columns. Any suggestions how to do this gratefully received.

Anomaly data.xlsx
	A
1	{
2	"bookmarks": {
3	"{016a6a50-3986-47a1-9009-de23a9f17954}": {
4	"dec": "+50°54'56\""
5	"fov": 1.0062208078568616e-244
6	"isVisibleMarker": true
7	"name": "Marker 285"
8	"nameI18n": "Marker 285"
9	"ra": "17h19m36.7s"
10	}
11	"{01df83f5-c398-4ebd-b46d-9cfa7b8d5d84}": {
12	"dec": "+50°53'33\""
13	"fov": 1.0062208078568616e-244
14	"isVisibleMarker": true
15	"name": "Marker 453"
16	"nameI18n": "Marker 453"
17	"ra": "18h07m57.2s"
mine data

 

[rlv01]

Your thread title says you want to extract data between quotes, but your post text says that you want to extract something you call the "marker number. " Those two things are not the same. Can you clarify? Better yet, add a Column B to your table which contains what you expect the result to be for your 17 example rows

tmp.xlsm
	A	B
1	{	?
2	"bookmarks": {	?
3	"{016a6a50-3986-47a1-9009-de23a9f17954}": {	?
4	"dec": "+50°54'56\""	?
5	"fov": 1.0062208078568616e-244	?
6	"isVisibleMarker": true	?
7	"name": "Marker 285"	?
8	"nameI18n": "Marker 285"	?
9	"ra": "17h19m36.7s"	?
10	}	?
11	"{01df83f5-c398-4ebd-b46d-9cfa7b8d5d84}": {	?
12	"dec": "+50°53'33\""	?
13	"fov": 1.0062208078568616e-244	?
14	"isVisibleMarker": true	?
15	"name": "Marker 453"	?
16	"nameI18n": "Marker 453"	?
17	"ra": "18h07m57.2s"	?
Sheet5

 

[Ed Harris]

Ok, this is my ideal output. If it makes it easier I can put up with the word Marker in there and s at the end of the Ra as easy to remove.

Anomaly data.xlsx
	A	B	C
1	{	marker	Ra
2	"bookmarks": {	285	17h19m36.7
3	"{016a6a50-3986-47a1-9009-de23a9f17954}": {	453	18h07m57.2
4	"dec": "+50°54'56\""
5	"fov": 1.0062208078568616e-244
6	"isVisibleMarker": true
7	"name": "Marker 285"
8	"nameI18n": "Marker 285"
9	"ra": "17h19m36.7s"
10	}
11	"{01df83f5-c398-4ebd-b46d-9cfa7b8d5d84}": {
12	"dec": "+50°53'33\""
13	"fov": 1.0062208078568616e-244
14	"isVisibleMarker": true
15	"name": "Marker 453"
16	"nameI18n": "Marker 453"
17	"ra": "18h07m57.2s"
18	}
mine data

 

[rlv01]

tmp.xlsm
	A	B	C
1	{	Marker	Ra
2	"bookmarks": {	285	17h19m36.7s
3	"{016a6a50-3986-47a1-9009-de23a9f17954}": {	453	18h07m57.2s
4	"dec": "+50°54'56\""
5	"fov": 1.0062208078568616e-244
6	"isVisibleMarker": true
7	"name": "Marker 285"
8	"nameI18n": "Marker 285"
9	"ra": "17h19m36.7s"
10	}
11	"{01df83f5-c398-4ebd-b46d-9cfa7b8d5d84}": {
12	"dec": "+50°53'33\""
13	"fov": 1.0062208078568616e-244
14	"isVisibleMarker": true
15	"name": "Marker 453"
16	"nameI18n": "Marker 453"
17	"ra": "18h07m57.2s"
Sheet2

Sub Test()

    Dim WS As Worksheet
    Dim RangeOfCells As Range, R As Range
    Dim I As Long
    Dim S As String
    Dim SA As Variant

    Set WS = ActiveSheet

    Set RangeOfCells = WS.Range("A2:A" & WS.Range("A" & WS.Rows.Count).End(xlUp).Row)    'last cell in column w/data

    RangeOfCells.Offset(0, 1).Resize(, 2).ClearContents

    I = 2
    For Each R In RangeOfCells

        S = Split(Application.Trim(R.Value), ":")(0)

        Select Case S
        Case Chr(34) & "name" & Chr(34), Chr(34) & "ra" & Chr(34)
            SA = Split(Application.Trim(Replace(R.Value, Chr(34), " ")), " ")
            If S = Chr(34) & "name" & Chr(34) Then
                WS.Cells(I, 2) = SA(UBound(SA))
            Else
                WS.Cells(I, 3) = SA(UBound(SA))
                I = I + 1
            End If
        End Select
    Next R
End Sub

 

[johnnyL]

Formula approach:

SheetNameChanger.xlsm
	A	B	C	D
1	{	marker	Ra
2	"bookmarks": {	285	17h19m36.7
3	"{016a6a50-3986-47a1-9009-de23a9f17954}": {	453	18h07m57.2
4	"dec": "+50°54'56\""
5	"fov": 1.0062208078568616e-244
6	"isVisibleMarker": true
7	"name": "Marker 285"
8	"nameI18n": "Marker 285"
9	"ra": "17h19m36.7s"
10	}
11	"{01df83f5-c398-4ebd-b46d-9cfa7b8d5d84}": {
12	"dec": "+50°53'33\""
13	"fov": 1.0062208078568616e-244
14	"isVisibleMarker": true
15	"name": "Marker 453"
16	"nameI18n": "Marker 453"
17	"ra": "18h07m57.2s"
18	}
19
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUBSTITUTE(RIGHT(A8,LEN(A8) - FIND("~",SUBSTITUTE(A8,"""","~",3)) - 7), """", "")
C2	C2	=SUBSTITUTE(SUBSTITUTE(RIGHT(A9,LEN(A9) - FIND("~",SUBSTITUTE(A9,"""","~",3))), "s", ""), """", "")
B3	B3	=SUBSTITUTE(RIGHT(A16,LEN(A16) - FIND("~",SUBSTITUTE(A16,"""","~",3)) - 7), """", "")
C3	C3	=SUBSTITUTE(SUBSTITUTE(RIGHT(A17,LEN(A17) - FIND("~",SUBSTITUTE(A17,"""","~",3))), "s", ""), """", "")

 

[Ed Harris]

tmp.xlsm
	A	B	C
1	{	Marker	Ra
2	"bookmarks": {	285	17h19m36.7s
3	"{016a6a50-3986-47a1-9009-de23a9f17954}": {	453	18h07m57.2s
4	"dec": "+50°54'56\""
5	"fov": 1.0062208078568616e-244
6	"isVisibleMarker": true
7	"name": "Marker 285"
8	"nameI18n": "Marker 285"
9	"ra": "17h19m36.7s"
10	}
11	"{01df83f5-c398-4ebd-b46d-9cfa7b8d5d84}": {
12	"dec": "+50°53'33\""
13	"fov": 1.0062208078568616e-244
14	"isVisibleMarker": true
15	"name": "Marker 453"
16	"nameI18n": "Marker 453"
17	"ra": "18h07m57.2s"
Sheet2

Sub Test()

    Dim WS As Worksheet
    Dim RangeOfCells As Range, R As Range
    Dim I As Long
    Dim S As String
    Dim SA As Variant

    Set WS = ActiveSheet

    Set RangeOfCells = WS.Range("A2:A" & WS.Range("A" & WS.Rows.Count).End(xlUp).Row)    'last cell in column w/data

    RangeOfCells.Offset(0, 1).Resize(, 2).ClearContents

    I = 2
    For Each R In RangeOfCells

        S = Split(Application.Trim(R.Value), ":")(0)

        Select Case S
        Case Chr(34) & "name" & Chr(34), Chr(34) & "ra" & Chr(34)
            SA = Split(Application.Trim(Replace(R.Value, Chr(34), " ")), " ")
            If S = Chr(34) & "name" & Chr(34) Then
                WS.Cells(I, 2) = SA(UBound(SA))
            Else
                WS.Cells(I, 3) = SA(UBound(SA))
                I = I + 1
            End If
        End Select
    Next R
End Sub

Awsome, this code works perfectly, many thanks