Address format in 1 cell

[vivekshanghvi]

Hi

I have multiple addresses in different cells
Address1 Address2 address3 (11 cells) Cell A1: cell A11

I want help so as to get all these 11 cells combined in 1 cell (in an address format) .. so there will be like 11 lines in a single cell (how you use Alt+enter to move to next line in the same cell)

Please help...

 

[vivekshanghvi]

Two more questions.

1. It seems that you have split to a new line where there is a comma in cell E2 but you have not split to a new line where there is a comma in cell D2. What is the logic there?
2. Why has the value from I2 changed when it appears in cell A5?

1. The problem is due to column width; it automatically moved to the new line but if i will increase the width it will be in a single line
2. I wasnt able to copy the number hence typed a random number

 

[Peter_SSs]

With your version of Excel you could use a formula using standard functions as shown in A5 below, or you could employ a user-defined function using the code below and as used in cell A6.
If you need help with how to implement the user-defined function, post back.

Function Addr(r As Range) As String
  Addr = Replace(Replace(Join(Filter(Split("|" & Join(Application.Index(r.Value, 1, 0), "|@|") & "|", "@"), "||", False), ""), "||", vbLf), "|", "")
End Function

vivekshanghvi.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Data recd
2	1	DEEPAK B	C/O SHI VILLA	2ND FLOOR, FLAT NO 2-A	DEB-KUTIR LANE, ULUBARI	MUMBAI	400056	MAHARASHTRA	09XXXX91715
3
4	Need data in this format
5	1 DEEPAK B C/O SHI VILLA 2ND FLOOR, FLAT NO 2-A DEB-KUTIR LANE, ULUBARI MUMBAI 400056 MAHARASHTRA 09XXXX91715
6	1 DEEPAK B C/O SHI VILLA 2ND FLOOR, FLAT NO 2-A DEB-KUTIR LANE, ULUBARI MUMBAI 400056 MAHARASHTRA 09XXXX91715
Sheet1

Cell Formulas
Range	Formula
A5	A5	=SUBSTITUTE(MID(IF(A2="","","|"&A2)&IF(B2="","","|"&B2)&IF(C2="","","|"&C2)&IF(D2="","","|"&D2)&IF(E2="","","|"&E2)&IF(F2="","","|"&F2)&IF(G2="","","|"&G2)&IF(H2="","","|"&H2)&IF(I2="","","|"&I2)&IF(J2="","","|"&J2)&IF(K2="","","|"&K2),2,500),"|",CHAR(10))
A6	A6	=Addr(A2:K2)

 

[vivekshanghvi]

With your version of Excel you could use a formula using standard functions as shown in A5 below, or you could employ a user-defined function using the code below and as used in cell A6.
If you need help with how to implement the user-defined function, post back.

Function Addr(r As Range) As String
  Addr = Replace(Replace(Join(Filter(Split("|" & Join(Application.Index(r.Value, 1, 0), "|@|") & "|", "@"), "||", False), ""), "||", vbLf), "|", "")
End Function

vivekshanghvi.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Data recd
2	1	DEEPAK B	C/O SHI VILLA	2ND FLOOR, FLAT NO 2-A	DEB-KUTIR LANE, ULUBARI	MUMBAI	400056	MAHARASHTRA	09XXXX91715
3
4	Need data in this format
5	1 DEEPAK B C/O SHI VILLA 2ND FLOOR, FLAT NO 2-A DEB-KUTIR LANE, ULUBARI MUMBAI 400056 MAHARASHTRA 09XXXX91715
6	1 DEEPAK B C/O SHI VILLA 2ND FLOOR, FLAT NO 2-A DEB-KUTIR LANE, ULUBARI MUMBAI 400056 MAHARASHTRA 09XXXX91715
Sheet1

Cell Formulas
Range	Formula
A5	A5	=SUBSTITUTE(MID(IF(A2="","","|"&A2)&IF(B2="","","|"&B2)&IF(C2="","","|"&C2)&IF(D2="","","|"&D2)&IF(E2="","","|"&E2)&IF(F2="","","|"&F2)&IF(G2="","","|"&G2)&IF(H2="","","|"&H2)&IF(I2="","","|"&I2)&IF(J2="","","|"&J2)&IF(K2="","","|"&K2),2,500),"|",CHAR(10))
A6	A6	=Addr(A2:K2)

Great Thanks.. it worked

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.