The only way my client can provide me these addresses is with the location is in one row: Location Name, street address, city.....and then the row below it contains the zip code. This repeats for 1600 lines.
我的客户可以向我提供这些地址的唯一方法是位置在一行:位置名称,街道地址,城市.....然后它下面的行包含邮政编码。重复1600行。
I, of course, need all the info on one line. Is there a genius out there than knows how to make short work of putting the zip code on the line above it?
当然,我需要一行上的所有信息。有没有天才,知道如何简单地将邮政编码放在上面的行上?
2 个解决方案
#1
2
If you have address in columns A, B and C in the first row and zip code in column A in second row, try the below formula,
如果第一行中的A,B和C列中有地址,第二行中的列A中有邮政编码,请尝试以下公式,
=INDEX(A:A,ROW()*2-1,1)&" " &INDEX(B:B,ROW()*2-1,1)& " " & INDEX(C:C,ROW()*2-1,1)& " " &INDEX(A:A,ROW()*2,1)
= INDEX(A:A,ROW()* 2-1,1)&“”&INDEX(B:B,ROW()* 2-1,1)&“”&INDEX(C:C,ROW()* 2-1,1)&“”&INDEX(A:A,ROW()* 2,1)
If you are starting from some other row other than row 1, you may have to modify the formula a little bit. Hope this helps.
如果从第1行以外的其他行开始,则可能需要稍微修改一下公式。希望这可以帮助。
#2
1
Use a formula to combine your columns.
使用公式来组合列。
Here we have some test data.
这里我们有一些测试数据。
In column D, specify a formula such as =A1 & ", " & B1 & " " & C1
在D列中,指定一个公式,例如= A1&“,”&B1&“”&C1
If you're no familiar with formulas, just use "=" to denote the start of one, and then use "&" to concatenate your values.
如果您不熟悉公式,只需使用“=”表示一个公式的开头,然后使用“&”来连接您的值。
As for as implementing this on a multi-row basis, you can easily do so. Once you drag your formula down, it'll auto increment the column names unless you specifically specified it not to. I won't get into that right now though.
至于在多行的基础上实现这一点,您可以轻松地这样做。一旦你拖下你的公式,它将自动增加列名,除非你没有特别指定它。我现在不会接受这个。
So what I would do is just add an IF statement in your formula to account for those rows which are not intended to be used. Using a formula such as this: =IF(B1="", "", A1 & ", " & B1 & " " & A2), I can get the following results.
所以我要做的就是在公式中添加一个IF语句来说明那些不打算使用的行。使用如下公式:= IF(B1 =“”,“”,A1&“,”&B1&“”&A2),我可以得到以下结果。
#1
2
If you have address in columns A, B and C in the first row and zip code in column A in second row, try the below formula,
如果第一行中的A,B和C列中有地址,第二行中的列A中有邮政编码,请尝试以下公式,
=INDEX(A:A,ROW()*2-1,1)&" " &INDEX(B:B,ROW()*2-1,1)& " " & INDEX(C:C,ROW()*2-1,1)& " " &INDEX(A:A,ROW()*2,1)
= INDEX(A:A,ROW()* 2-1,1)&“”&INDEX(B:B,ROW()* 2-1,1)&“”&INDEX(C:C,ROW()* 2-1,1)&“”&INDEX(A:A,ROW()* 2,1)
If you are starting from some other row other than row 1, you may have to modify the formula a little bit. Hope this helps.
如果从第1行以外的其他行开始,则可能需要稍微修改一下公式。希望这可以帮助。
#2
1
Use a formula to combine your columns.
使用公式来组合列。
Here we have some test data.
这里我们有一些测试数据。
In column D, specify a formula such as =A1 & ", " & B1 & " " & C1
在D列中,指定一个公式,例如= A1&“,”&B1&“”&C1
If you're no familiar with formulas, just use "=" to denote the start of one, and then use "&" to concatenate your values.
如果您不熟悉公式,只需使用“=”表示一个公式的开头,然后使用“&”来连接您的值。
As for as implementing this on a multi-row basis, you can easily do so. Once you drag your formula down, it'll auto increment the column names unless you specifically specified it not to. I won't get into that right now though.
至于在多行的基础上实现这一点,您可以轻松地这样做。一旦你拖下你的公式,它将自动增加列名,除非你没有特别指定它。我现在不会接受这个。
So what I would do is just add an IF statement in your formula to account for those rows which are not intended to be used. Using a formula such as this: =IF(B1="", "", A1 & ", " & B1 & " " & A2), I can get the following results.
所以我要做的就是在公式中添加一个IF语句来说明那些不打算使用的行。使用如下公式:= IF(B1 =“”,“”,A1&“,”&B1&“”&A2),我可以得到以下结果。