在 Excel 中拆分地址字段 [英] Split address field in Excel

问题描述

我有一个带有地址单元格的 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.

全部替换可通过按 CTRL+H 实现.

Replace All is available via press CTRL+H.

如果您不确定该字符是否在您的数据中 - 请在替换前通过搜索进行检查.

If you're not sure the character is in your data - check via search before replacement.

这篇关于在 Excel 中拆分地址字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！