I got spreadsheet including annoying values and struggle to this. Required data is in each cell of column A. Value of a cell is {"p0":70,"u3":71,"s7110":40},t45,{"t78":60,"s3310":15},p37,p36,{"p29":44,"s8110":95},p85,p14,{"s2710":41},u47
. Number of such values is about 1000. I have to parse these values. I need strings and objects from such values. I can ignore order of parsed value. I cannot parse manually. So I decided to use script.
我得到了电子表格,包括恼人的价值观和挣扎。所需数据在列a的每个单元格中。单元格的值为{“p0”:70,“u3”:71,“s7110”:40},t45,{“t78”:60,“s3310”:15},p37,p36,{“p29”:44,“s8110”:95},p85, p85,p14。这些值的数量大约是1000。我必须解析这些值。我需要这些值中的字符串和对象。我可以忽略解析值的顺序。我不能手动解析。所以我决定使用脚本。
I tried to parse using "split".
我尝试使用“split”来解析。
var sheet = SpreadsheetApp.getActiveSheet();
var values = sheet.getRange("A1:A" + sheet.getLastRow()).getValues();
var result = [];
for (var i = 0; i < values.length; i++)
{
result.push(values[i][0].split(","));
}
I got t45,p37,p36,p85,p14,u47
as string. But "split" also splits all objects. For example, {"p0":70,"u3":71,"s7110":40}
is split to {"p0":70
, "u3":71
, "s7110":40}
. Is there way to solve this?
得到t45 p37 p36 p85 p14 u47作为字符串。但是“分割”也分割所有的对象。例如,{“p0”:70年,“u3”:71年,“s7110”:40 }是分裂{“p0”:70年,“u3”:71年,“s7110”:40 }。有办法解决这个问题吗?
Sample values are this. Each line is in cell A1, A2, A3, A4, A5.
这个示例值。每一行都在A1 A2 A3 A4 A5中。
{"p0":70,"u3":71,"s7110":40},t45,{"t78":60,"s3310":15},p37,p36,{"p29":44,"s8110":95},p85,p14,{"s2710":41},u47
s6610,{"t25":70,"u8":43,"p35":86},u85,u74,{"s7710":83},{"p70":70,"u67":84},{"u71":43,"s1210":73},{"u45":84,"s710":15},{"u14":79,"p22":45},p31
u73,u12,{"t51":98,"u57":96},u31,p41,s1110,s6610,p55,{"t57":71,"s7510":83,"u62":17},u73
t50,{"t83":22,"p18":76},{"p47":12,"s8710":18,"u11":35},{"t14":74,"u72":51},{"p74":21,"t77":77},{"u62":84,"s3010":11},p81,u36,p67,{"t79":12,"u2":70,"s6010":98}
{"u54":51,"t31":31},t56,s4110,{"s3110":84,"t25":92,"p80":19},s3210,{"p65":54,"s8510":45},{"t73":78,"s6210":11},{"s2110":98,"p11":16},{"p61":55,"t88":75},p38
Thank you so much for your time. And I'm sorry for my immature question.
非常感谢您的时间。我为我不成熟的问题感到抱歉。
2 个解决方案
#1
1
B1:
B1:
=ARRAYFORMULA(REGEXEXTRACT(A1:A5&",",REGEXREPLACE(REGEXREPLACE(A1:A5&",","{.*?}","($0)"),"([A-Za-z]\d+),","($1),")))
We are enclosing all objects and strings except the commas ,
with ()
and then extracting them later.
除了逗号、with()之外,我们将所有对象和字符串都封装在一起,然后稍后提取它们。
EDIT:An easier anchor: The ,
commas to split by are NOT followed by "
编辑:一个更简单的锚:分隔的逗号后面不跟着"
=ARRAYFORMULA(SUBSTITUTE(SPLIT(SUBSTITUTE(A1:A5,","&CHAR(34),"????"),","),"????",","&CHAR(34)))
=ARRAYFORMULA(split(REGEXREPLACE(A1:A5,"(,)([^"&CHAR(34)&"])","????$2"),"????"))
#2
1
You can use RegExp to replace characters with something unique that can be found without affecting anything else.
您可以使用RegExp将字符替换为可以找到的惟一字符,而不影响任何其他内容。
function myFunction() {
var L,newArray,thisElement;
var myStrng = '{"p0":70,"u3":71,"s7110":40},t45,{"t78":60,"s3310":15},p37,p36,{"p29":44,"s8110":95},p85,p14,{"s2710":41},u47 \
s6610,{"t25":70,"u8":43,"p35":86},u85,u74,{"s7710":83},{"p70":70,"u67":84},{"u71":43,"s1210":73},{"u45":84,"s710":15},{"u14":79,"p22":45},p31 \
u73,u12,{"t51":98,"u57":96},u31,p41,s1110,s6610,p55,{"t57":71,"s7510":83,"u62":17},u73 \
t50,{"t83":22,"p18":76},{"p47":12,"s8710":18,"u11":35},{"t14":74,"u72":51},{"p74":21,"t77":77},{"u62":84,"s3010":11},p81,u36,p67,{"t79":12,"u2":70,"s6010":98} \
{"u54":51,"t31":31},t56,s4110,{"s3110":84,"t25":92,"p80":19},s3210,{"p65":54,"s8510":45},{"t73":78,"s6210":11},{"s2110":98,"p11":16},{"p61":55,"t88":75},p38';
var re = new RegExp("\},","g");
var parsedObj = myStrng.replace(re,"}zq^");//Replace all }, characters with }zq^
//Logger.log(parsedObj)
parsedObj = parsedObj.replace(/,\{/g,"zq^{");//Replace all ,{ characters with zq^{
//Logger.log(parsedObj)
parsedObj = parsedObj.replace(/\}\{/g,"}zq^{");//Replace all back to back brackets
parsedObj = parsedObj.replace(/\} \{/g,"}zq^{");//Replace all back to back brackets with a space between
parsedObj = parsedObj.split("zq^");//split on zq^
L = parsedObj.length;
newArray = [];
for (var i=0;i<L;i++) {
thisElement = parsedObj[i];
//Logger.log('thisElement: ' + thisElement)
if (thisElement.indexOf("{") !== -1) {
newArray.push(thisElement);
continue;
}
if (thisElement.indexOf(",") !== -1) {
thisElement = thisElement.split(",");
for (var j =0;j<thisElement.length;j++) {
newArray.push(thisElement[j]);
}
continue;
}
if (thisElement.indexOf(" ") !== -1) {
thisElement = thisElement.split(" ");
for (var j =0;j<thisElement.length;j++) {
newArray.push(thisElement[j]);
}
continue;
}
newArray.push(thisElement);
}
L = newArray.length;
for (var i=0;i<L;i++) {
Logger.log(newArray[i])
}
}
#1
1
B1:
B1:
=ARRAYFORMULA(REGEXEXTRACT(A1:A5&",",REGEXREPLACE(REGEXREPLACE(A1:A5&",","{.*?}","($0)"),"([A-Za-z]\d+),","($1),")))
We are enclosing all objects and strings except the commas ,
with ()
and then extracting them later.
除了逗号、with()之外,我们将所有对象和字符串都封装在一起,然后稍后提取它们。
EDIT:An easier anchor: The ,
commas to split by are NOT followed by "
编辑:一个更简单的锚:分隔的逗号后面不跟着"
=ARRAYFORMULA(SUBSTITUTE(SPLIT(SUBSTITUTE(A1:A5,","&CHAR(34),"????"),","),"????",","&CHAR(34)))
=ARRAYFORMULA(split(REGEXREPLACE(A1:A5,"(,)([^"&CHAR(34)&"])","????$2"),"????"))
#2
1
You can use RegExp to replace characters with something unique that can be found without affecting anything else.
您可以使用RegExp将字符替换为可以找到的惟一字符,而不影响任何其他内容。
function myFunction() {
var L,newArray,thisElement;
var myStrng = '{"p0":70,"u3":71,"s7110":40},t45,{"t78":60,"s3310":15},p37,p36,{"p29":44,"s8110":95},p85,p14,{"s2710":41},u47 \
s6610,{"t25":70,"u8":43,"p35":86},u85,u74,{"s7710":83},{"p70":70,"u67":84},{"u71":43,"s1210":73},{"u45":84,"s710":15},{"u14":79,"p22":45},p31 \
u73,u12,{"t51":98,"u57":96},u31,p41,s1110,s6610,p55,{"t57":71,"s7510":83,"u62":17},u73 \
t50,{"t83":22,"p18":76},{"p47":12,"s8710":18,"u11":35},{"t14":74,"u72":51},{"p74":21,"t77":77},{"u62":84,"s3010":11},p81,u36,p67,{"t79":12,"u2":70,"s6010":98} \
{"u54":51,"t31":31},t56,s4110,{"s3110":84,"t25":92,"p80":19},s3210,{"p65":54,"s8510":45},{"t73":78,"s6210":11},{"s2110":98,"p11":16},{"p61":55,"t88":75},p38';
var re = new RegExp("\},","g");
var parsedObj = myStrng.replace(re,"}zq^");//Replace all }, characters with }zq^
//Logger.log(parsedObj)
parsedObj = parsedObj.replace(/,\{/g,"zq^{");//Replace all ,{ characters with zq^{
//Logger.log(parsedObj)
parsedObj = parsedObj.replace(/\}\{/g,"}zq^{");//Replace all back to back brackets
parsedObj = parsedObj.replace(/\} \{/g,"}zq^{");//Replace all back to back brackets with a space between
parsedObj = parsedObj.split("zq^");//split on zq^
L = parsedObj.length;
newArray = [];
for (var i=0;i<L;i++) {
thisElement = parsedObj[i];
//Logger.log('thisElement: ' + thisElement)
if (thisElement.indexOf("{") !== -1) {
newArray.push(thisElement);
continue;
}
if (thisElement.indexOf(",") !== -1) {
thisElement = thisElement.split(",");
for (var j =0;j<thisElement.length;j++) {
newArray.push(thisElement[j]);
}
continue;
}
if (thisElement.indexOf(" ") !== -1) {
thisElement = thisElement.split(" ");
for (var j =0;j<thisElement.length;j++) {
newArray.push(thisElement[j]);
}
continue;
}
newArray.push(thisElement);
}
L = newArray.length;
for (var i=0;i<L;i++) {
Logger.log(newArray[i])
}
}