Selecting certain information from a string

[Ramballah]

Hi,

A certain formula gives me a certain string of information that looks like:

{"success":true,"lowest_price":"7,70€","volume":"25","median_price":"7,69€"} {"success":true,"lowest_price":"11,32€","volume":"18","median_price":"10,87€"} {"success":true,"lowest_price":"8,80€","volume":"32","median_price":"8,88€"} {"success":true,"lowest_price":"15,20€","volume":"31","median_price":"14,20€"} {"success":true,"lowest_price":"7,01€","volume":"21","median_price":"7,41€"} {"success":true,"lowest_price":"9,23€","volume":"51","median_price":"9,39€"} {"success":true,"lowest_price":"17,21€","volume":"15","median_price":"18,01€"} {"success":true,"lowest_price":"3,65€","volume":"46","median_price":"3,62€"} {"success":true,"lowest_price":"4,05€","volume":"47","median_price":"3,68€"}

every row containing stuff like this basically. Now what I want in the column after it (lets say column B) is to only get the number that comes after the lowest price. so 7,70 - 11,32 - 8,80 you get the point. For column C it would be what comes after the median price. For some reason i can't get it to work so if someone got a nice formula for me that would be nice! thanks in advance

 

[lrobbo314]

Here is a way using VBA.

REGEX
	A	B	C
1	{"success":true,"lowest_price":"7,70€","volume":"25","median_price":"7,69€"}	7,70	7,69
2	{"success":true,"lowest_price":"11,32€","volume":"18","median_price":"10,87€"}	11,32	10,87
Sheet4

Cell Formulas
Range	Formula
B1:B2	B1	=Get_Low_Price(A1)
C1:C2	C1	=Get_Med_Price(A1)

Function Get_Low_Price(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = """lowest_price"":""(\S*?)€"
    Get_Low_Price = .Execute(s)(0).submatches(0)
End With
End Function

Function Get_Med_Price(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = """median_price"":""(\S*?)€"
    Get_Med_Price = .Execute(s)(0).submatches(0)
End With
End Function

 

[Ramballah]

Here is a way using VBA.

REGEX
	A	B	C
1	{"success":true,"lowest_price":"7,70€","volume":"25","median_price":"7,69€"}	7,70	7,69
2	{"success":true,"lowest_price":"11,32€","volume":"18","median_price":"10,87€"}	11,32	10,87
Sheet4

Cell Formulas
Range	Formula
B1:B2	B1	=Get_Low_Price(A1)
C1:C2	C1	=Get_Med_Price(A1)

Function Get_Low_Price(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = """lowest_price"":""(\S*?)€"
    Get_Low_Price = .Execute(s)(0).submatches(0)
End With
End Function

Function Get_Med_Price(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = """median_price"":""(\S*?)€"
    Get_Med_Price = .Execute(s)(0).submatches(0)
End With
End Function

Hello, thank you for your answer (sorry for the late reply i fell asleep ) but when I was implementing this, this is what I get:

Is there some setting that I can change? I am not tied by any organization so this is baffling to me.
Also dont mind the double lines and randomness in this sheet, this is a test sheet.
and while im at it, is it possible to have the workbook do a code that replaces = with = ? Because the webservice function doesn't update unless you kind of replace all = with = again. However I was wondering if its possible to have the workbook automatically do that upon opening instead of me having to do it manually each time.

 

[lrobbo314]

I don't know why you would be getting that error.

This should work.

EXCEL
	A	B	C	D
1	{"success":true,"lowest_price":"7,70€","volume":"25","median_price":"7,69€"}		7,70	7,69
2	{"success":true,"lowest_price":"11,32€","volume":"18","median_price":"10,87€"}		11,32	10,87
Sheet4

Cell Formulas
Range	Formula
C1:C2	C1	=MID(A1,FIND("""lowest_price"":""",A1)+LEN("""lowest_price"":"""),FIND("€",A1)-(FIND("""lowest_price"":""",A1)+LEN("""lowest_price"":""")))
D1:D2	D1	=MID(A1,FIND("""median_price"":""",A1)+LEN("""median_price"":"""),(FIND("}",A1)-2)-(FIND("""median_price"":""",A1)+LEN("""median_price"":""")))

 

[Ramballah]

I don't know why you would be getting that error.

This should work.

EXCEL
	A	B	C	D
1	{"success":true,"lowest_price":"7,70€","volume":"25","median_price":"7,69€"}		7,70	7,69
2	{"success":true,"lowest_price":"11,32€","volume":"18","median_price":"10,87€"}		11,32	10,87
Sheet4

Cell Formulas
Range	Formula
C1:C2	C1	=MID(A1,FIND("""lowest_price"":""",A1)+LEN("""lowest_price"":"""),FIND("€",A1)-(FIND("""lowest_price"":""",A1)+LEN("""lowest_price"":""")))
D1:D2	D1	=MID(A1,FIND("""median_price"":""",A1)+LEN("""median_price"":"""),(FIND("}",A1)-2)-(FIND("""median_price"":""",A1)+LEN("""median_price"":""")))

Thank you, do you also have a vba macro for me that whenever I open the workblook the code will replace all = signs with just =. This is so formulas like webservice refresh properly?