Hello!
Thank you in advance for all the support, I'm wondering if you could help me with an approach to this:
10003|TRUE|[{*day*:*mon*,*amount*:3463.98},{*nig*:*tue*,*amount*:77.09}]
I have a column of data that looks like this, all of the above is in the same cell, I need to extract the numbers, i.e. 3463.98 in one cell and 77.09 in a different cell.
Does anyone have any idea of which formula I can use? I've tried with a combination left, mid, find, etc but I haven't found any success.
Are the numbers always in the same position? Is a user-defined function OK?
10003|TRUE|[{*day*:*mon*,*amount*:3463.98},{*nig*:*tue*,*amount*:77.09}] A3: =--MidWd(SUBSTITUTE(A2, "}", ":"), 2, 1, ":") A4: =--MidWd(SUBSTITUTE(A2, "}", ":"), 5, 1, ":")
Entia non sunt multiplicanda sine necessitate
1. Enter formula in B2
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"}",":"),":",REPT(" ",99)),99*2,99))/1
2. Enter formula in C2
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A2,"}",":"),":",REPT(" ",99)),99*5,99))/1
v A B C 1 2 10003|TRUE|[{*day*:*mon*,*amount*:3463.98},{*nig*:*tue*,*amount*:77.09}] 3463.98 77.09
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
1] Data put in cell A1
2] B1, formula copied across right until blank :
=IFERROR(LOOKUP(9^9,--MID($A1,FIND("@",SUBSTITUTE($A1,"*amount*:","@",COLUMN(A1)))+9,ROW($1:$99))),"")
p.s. can extract all numbers behind specified text "amount".
Regards
Bosco
Last edited by Bosco; 04-23-2019 at 12:57 PM.
Here is another one with a single formula
Drag formula across
=MID(SUBSTITUTE(SUBSTITUTE($A1,"}",":"),":",REPT(" ",99)),99*INDEX({2,5},COLUMNS($A:A)),99)/1
Last edited by AlKey; 04-23-2019 at 12:51 PM.
This one works great, just one question, if a cell reads:
10007|TRUE|[{*currency*:*USD*,*amount*:14824.98},{*currency*:*ZEC*,*amount*:0},{*currency*:*XRP*,*amount*:0},{*currency*:*LTC*,*amount*:1},{*currency*:*ETH*,*amount*:0.7},{*currency*:*BTC*,*amount*:0},{*currency*:*DASH*,*amount*:0.093602},{*currency*:*ETC*,*amount*:0},{*currency*:*XMR*,*amount*:0}]
Meaning I can get the value for the first and second amounts, how would you get the value of the other amounts, i.e. instead of 2 and then 5, what would be the numbers?
As I understand, the string is in A1 and the values need to be extracted in columns:
Enter formula in B1 and drag across until you see blanks
Formula:
Please Login or Register to view this content.
v A B C D E F G H I J K 1 10007|TRUE|[{*currency*:*USD*,*amount*:14824.98},{*currency*:*ZEC*,*amount*:0},{*currency*:*XRP*,*amount*:0},{*currency*:*LTC*,*amount*:1},{*currency*:*ETH*,*amount*:0.7},{*currency*:*BTC*,*amount*:0},{*currency*:*DASH*,*amount*:0.093602},{*currency*:*ETC*,*amount*:0},{*currency*:*XMR*,*amount*:0}] 14824.98 0 0 1 0.7 0 0.093602 0 0
Another drag across formula :
=IFERROR(0+TRIM(MID(SUBSTITUTE(SUBSTITUTE("}"&$A1,"t*:","}"),"}",REPT(" ",300)),COLUMN(A1)*600,300)),"")
Regards
Bosco
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks