问题描述
我有一个带有地址单元格的 excel 文件.
I have an excel file with cells with addresses.
3 个不同的数据示例:
3 different data examples:
123 Main Street/n Apartment2 /n New York, NY 10001
123 Main Street/n New York, NY 10001
123 Main Street
我想把它分成三个独立的领域.但是,如上述数据所示,有些/n 会出现 0 次,有些会出现 2 次.有些会出现 3 次.
I would like to split it into three separate fields. However, as the above data shows, some will have 0 occurrences of /n, some will have 2 occurrences. and some will have 3 occurrences.
我可以处理一个事件:=LEFT(E6, SEARCH("/n",E6,1)-2)=RIGHT(Export!E6,LEN(Export!E6)- SEARCH("/n",Export!E6,1)-1)
I can handle one occurrence, with: =LEFT(E6, SEARCH(" /n",E6,1)-2) =RIGHT(Export!E6,LEN(Export!E6)- SEARCH("/n",Export!E6,1)-1)
但是,当/n 的数量可变时,我的公式开始分解
However, my formula starts breaking down when there are variable number of /n
有什么建议吗?
推荐答案
值得一答的解决方案:
将/n"替换为不在您的数据中的任何单个字符,例如@#$^~
- 并使用 Text to Columns
.
Replace "/n" to any single character which is NOT in your data, e.g. @#$^~
- and use Text to Columns
.
全部替换可通过按 + 实现.
Replace All is available via press +.
如果您不确定该字符是否在您的数据中 - 请在替换前通过搜索进行检查.
If you're not sure the character is in your data - check via search before replacement.
这篇关于在 Excel 中拆分地址字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!