excel - 是否可以在随机填充的不同长度的单元格上结合 LEN 执行 LEFT 或 RIGHT 功能？

Question

序言:我有一个问题，我正在使用一个非常简单的解决方案。但是，为了学习，我想知道是否有任何 Excel Guru 对我的问题有替代解决方案。

我有一个包含大约 4500 个美国和加拿大城市的列的 excel 表。
以下是列中数据格式的示例:

安大略省多伦多

佛罗里达州 jackson 维尔

温哥华, BC

在工作簿的另一张纸上，我随机选择了一个城市。

所以我使用以下功能:

=INDEX(DATA!$E$2:$E$4576,RANDBETWEEN(1,4574),1)

该函数从 4500 个城市中随机选择；问题是我想填充两个单元格，一个包含城市，一个包含省/州。

通常，从格式化为“城市，省”的单元格中提取城市/省非常简单。

我会做这样的事情:

对于州或省:

=RIGHT(A1,2)

城市:

=LEFT(A1,LEN(A1)-4)

我的问题在于城市/州的随机选择。

我无法使用: 选择城市

=LEFT(INDEX(DATA!$E$2:$E$4576,RANDBETWEEN(1,4574),1),LEN(INDEX(DATA!$E$2:$E$4576,RANDBETWEEN(1,4574),1))-4)

因为当我计算 LEN 函数时，它会随机选择另一个城市并使用该长度进行计算。因此，取决于一个函数中随机城市选择的组合，有些会很短，有些会很长(有些会很准确)。

相同问题的扩展是当我执行 RIGHT 字符串函数来获取状态时，它将来自另一个随机选择的值，因此它不会(必然)与城市匹配。

我能想到的最简单的事情，也是我所做的，是在一个单元格中进行随机选择，然后在该单元格上执行 RIGHT 和 LEFT 函数。

像这样:

//populate cell A1 randomly
A1=INDEX(DATA!$E$2:$E$4576,RANDBETWEEN(1,4574),1)

//pull the province/state form A1
B1=RIGHT(A1,2)

//pull the city name from cell A1
C1=LEFT(A1,LEN(A1)-4)

如果有另一种方法可以在 RANDOM 选择上执行 LEFT/LEN 功能，而不必播放另一个单元格，我只是好奇。如果可能的话，我想避免使用 VBA。可能没有解决方案，但我希望比我了解更多的人会有一些想法!

Answer 1

试试这个公式得到一个没有省份的随机城市
=TRIM(LEFT(SUBSTITUTE(INDEX(DATA!$E$2:$E$4576,RANDBETWEEN(1,4575)),",",REPT(" ",99)),99))
注意:您有 4575 行数据，因此您需要 RANDBETWEEN 上升到 4575 否则您不考虑最后一行

该公式将逗号替换为 99 个空格，然后取前 99 个字符并删除空格 - 这是一种无需再次使用 RANDBETWEEN 即可获得 City 的方法。

现在假设您的城市都不同，您可以使用 VLOOKUP 使用通配符查找该城市并获取省份，例如在 A2 中使用上述公式 在 B2 中使用此公式
=RIGHT(VLOOKUP(A2&",*",DATA!$E$2:$E$4576,1,0),2)
如果城市可能有重复，那么您可以在 B2 中使用此“数组公式”
=RIGHT(INDEX(DATA!$E:$E,SMALL(IF(LEFT(DATA!$E$2:$E$4576,LEN(A2)+1)=A2&",",ROW(DATA!$E$2:$E$4576)),RANDBETWEEN(1,COUNTIF(DATA!$E$2:$E$4576,A2&",*")))),2)
用 CTRL+SHIFT+ENTER 确认 CTRL+SHIFT+ENTER

您不能保证第二个公式返回的省份与第一个公式中选择的单元格中的省份相同(除非该城市没有重复项)，但这并不重要。如果有重复的城市，它将随机选择一个省份