如何在Excel单元格中美化json文本?

Question

在excel文件中，有很多json文本，如下面

{"component":"{{$labels.component}}","container":"{{$labels.container}}","daemonset":"{{$labels.daemonset}}","directory":"{{$labels.directory}}","figure":"{{$value}}","instance":"{{$labels.instance}}","job":"{{$labels.job}}","name":"{{$labels.name}}","namespace":"{{$labels.namespace}}","pod":"{{$labels.pod}}","reason":"{{$labels.reason}}"}

我想这样美化json文本

{
"component": "{{$labels.component}}",
"container": "{{$labels.container}}",
"daemonset": "{{$labels.daemonset}}",
"directory": "{{$labels.directory}}",
"figure": "{{$value}}",
"instance": "{{$labels.instance}}",
"job": "{{$labels.job}}",
"name": "{{$labels.name}}",
"namespace": "{{$labels.namespace}}",
"pod": "{{$labels.pod}}",
"reason": "{{$labels.reason}}"
}

是否有任何方法可以在我的excel文件中的任何单元格?
谢谢!

Answer 1

如果你有Excel 365当前通道，那么你可以使用这个公式-你的json在单元格A1

=LET(json,A1,
r,SUBSTITUTE(json,",","," & CHAR(10)),
"{" & CHAR(10) & MID(r,3,LEN( r)-3) & CHAR(10) & "}")

首先用逗号加换行字符(10)替换逗号。然后处理第一个和最后一个换行符。