英文:
Google Sheets - combining split with regexextract/regexreplace
问题
我正在处理一个工作簿，其中一个列设置正确。基本上，它的设置是这样的，这个值：
这是Foo，我讨厌它
这是Bar，我喜欢它
这是Baz
显示为以逗号分隔的形式，如下：
这是Foo，我讨厌它，这是Bar，我喜欢它，这是Baz
我需要找到一种分割这些值的方法。我最初考虑使用逗号作为分隔符来进行分割，但由于一些独立的值中含有逗号，所以这种方法不适用。
我考虑使用正则表达式来捕获逗号后面跟着大写字母的情况（如,[A-Z]），然后将其与分割函数结合使用。这种方法可行吗？基本上是使用逗号和大写字母作为分隔符来分割单元格。
尝试使用Regexreplace来将逗号后面跟着大写字母的情况替换为非逗号的分隔符：
=REGEXREPLACE(A2, ",[A-Z]", ";")
然后得到类似以下的结果：
这是Foo，我讨厌它;his是Bar，我喜欢它;nd这是Baz
然后可以使用分号进行分割，但是会丢失其他值的第一个字符。
英文:
I'm working on a workbook that has one column setup correctly. Essentially, it is setup such that this value:
> This is Foo, and I hate it<br>
> This is Bar, and I like it<br>
> And this is Baz
is displayed as separated by comma, like:
> This is Foo, and I hate it,This is Bar, and I like it,And this is Baz
I need to find a way to split these values. I first thought of using Split with comma as delimiter, but it won't work because some individual values have commas in them.
I'm thinking of using regex to capture a comma followed by a capital letter (like ,[A-Z]) and to combine this with the split function. Is this possible? Basically to split the cell using the comma and capital letter as delimiter.
Tried using Regexreplace to replace a comma followed by a capital letter with a delimiter that's not a comma:
=REGEXREPLACE(A2, ",[A-Z]", ";")
Which then results to something like
This is Foo, and I hate it;his is Bar, and I like it;nd this is Baz
I can then split them using a semicolon, but then I lose the first character of the other values.
答案1
得分: 2
=如果你想反过来使用SUBSTITUTE呢？你可以将逗号和空格替换为分号，然后按逗号拆分：
=SPLIT(SUBSTITUTE(A1, ", ", "; "), ",")
=如果你想要恢复逗号，可以使用ARRAYFORMULA和SUBSTITUTE来包装该公式：
=ARRAYFORMULA(SUBSTITUTE(SPLIT(SUBSTITUTE(A1, ", ", "; "), ","), ";", ","))
英文:
And if you use SUBSTITUTE the other way round?, You change comma and space with semicolon, then split by commas:
=SPLIT(SUBSTITUTE(A1,", ","; "),",")
And if you want back the commas, use ARRAYFORMULA and SUBSTITUTE wrapping that formula:
=ARRAYFORMULA(SUBSTITUTE(SPLIT(SUBSTITUTE(A1,", ","; "),","),";",","))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论