在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

我想将其分为三个单独的字段.但是，如以上数据所示，一些将出现0次/n，某些将发生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屋！