Formula to convert text to columns at changing character

[RAJESH1960]

Hello again,
I am trying to convert this data from text to columns at each change in character"," with the help of a formula in each of the 8 cells, as the data may contain a maximum 8 such characters. I got the first one right with DanteAmor's formula, but I am not able to get the remaining formulas. Please note that there are 2",," in some cases in which case I may get a blank cell. That will be not a problem.

Query to seperate number and date with formula.xlsx
	A	B	C	D	E	F	G	H	I
1	Address	1	2	3	4	5	6	7	8
2	NO 2420, 2ND FLOOR, BUILDING NO 25B, MITTAL ESTATE, ANDHERI(E)	NO 2420,
3	#129, 4th Block,, 2nd Stage, HBR Layout, Bangalore-560043, Mob:12345678	#129,
4
5	1234 Situated MG Road, KG Halli Near GMR Circle, Bangalore	1234 Situated MG Road,
6	2613 Kamraj Road, Bangalore - 560042	2613 Kamraj Road,
7	No.1235, Ground Floor,, PB Chambers, Opp. Sun Medicals, Kamaraj Road, Bangalore-560042	No.1235,
8	420, NKK Complex, SP Road, Bangalore	420,
9	2510, PANDESARA, Behind RACHNA ENTERPRISES, SURAT	2510,
10
11	840- Agraham Street, Erode	840- Agraham Street,
12
Sheet2

Cell Formulas
Range	Formula
B2:B12	B2	=IFERROR(LEFT(A2,FIND(",",A2)),"")

 

[shift-del]

Hi

=IFERROR(TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(A2,",","</z><z>")&"</z></y>","//z")),"")

 

[RAJESH1960]

Hi

=IFERROR(TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(A2,",","</z><z>")&"</z></y>","//z")),"")

Hello, I am able to get the first part in column B. How do I get the remaining data in other columns.?

 

[jtakw]

Hi,

This should do it:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Address	1	2	3	4	5	6	7	8
2	NO 2420, 2ND FLOOR, BUILDING NO 25B, MITTAL ESTATE, ANDHERI(E)	NO 2420	2ND FLOOR	BUILDING NO 25B	MITTAL ESTATE	ANDHERI(E)
3	#129, 4th Block,, 2nd Stage, HBR Layout, Bangalore-560043, Mob:12345678	#129	4th Block		2nd Stage	HBR Layout	Bangalore-560043	Mob:12345678
4
5	1234 Situated MG Road, KG Halli Near GMR Circle, Bangalore	1234 Situated MG Road	KG Halli Near GMR Circle	Bangalore
6	2613 Kamraj Road, Bangalore - 560042	2613 Kamraj Road	Bangalore - 560042
7	No.1235, Ground Floor,, PB Chambers, Opp. Sun Medicals, Kamaraj Road, Bangalore-560042	No.1235	Ground Floor		PB Chambers	Opp. Sun Medicals	Kamaraj Road	Bangalore-560042
8	420, NKK Complex, SP Road, Bangalore	420	NKK Complex	SP Road	Bangalore
9	2510, PANDESARA, Behind RACHNA ENTERPRISES, SURAT	2510	PANDESARA	Behind RACHNA ENTERPRISES	SURAT
10
11	840- Agraham Street, Erode	840- Agraham Street	Erode
Sheet971

Cell Formulas
Range	Formula
B2:I11	B2	=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100))

 

[RAJESH1960]

Hi,

This should do it:

Book3.xlsx
	A	B	C	D	E	F	G	H	I
1	Address	1	2	3	4	5	6	7	8
2	NO 2420, 2ND FLOOR, BUILDING NO 25B, MITTAL ESTATE, ANDHERI(E)	NO 2420	2ND FLOOR	BUILDING NO 25B	MITTAL ESTATE	ANDHERI(E)
3	#129, 4th Block,, 2nd Stage, HBR Layout, Bangalore-560043, Mob:12345678	#129	4th Block		2nd Stage	HBR Layout	Bangalore-560043	Mob:12345678
4
5	1234 Situated MG Road, KG Halli Near GMR Circle, Bangalore	1234 Situated MG Road	KG Halli Near GMR Circle	Bangalore
6	2613 Kamraj Road, Bangalore - 560042	2613 Kamraj Road	Bangalore - 560042
7	No.1235, Ground Floor,, PB Chambers, Opp. Sun Medicals, Kamaraj Road, Bangalore-560042	No.1235	Ground Floor		PB Chambers	Opp. Sun Medicals	Kamaraj Road	Bangalore-560042
8	420, NKK Complex, SP Road, Bangalore	420	NKK Complex	SP Road	Bangalore
9	2510, PANDESARA, Behind RACHNA ENTERPRISES, SURAT	2510	PANDESARA	Behind RACHNA ENTERPRISES	SURAT
10
11	840- Agraham Street, Erode	840- Agraham Street	Erode
Sheet971

Cell Formulas
Range	Formula
B2:I11	B2	=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),COLUMNS($B2:B2)*100-99,100))

How about the formulas in column C to I.?

 

[jtakw]

How about the formulas in column C to I.?

Notice my sample, B2 formula is copied Across and Down to I11.

 

[RAJESH1960]

Notice my sample, B2 formula is copied Across and Down to I11.

Oh yeah, I just have to drag it across. Thanks jtakw.

 

[jtakw]

You're welcome, thanks for the feedback.

 

[shift-del]

Hello, I am able to get the first part in column B. How do I get the remaining data in other columns.?

I didn't recognize that you are on an older version of Excel.
Try this amended formula instead:

=IFERROR(INDEX(FILTERXML("<y><z>"&SUBSTITUTE($A2,",","</z><z>")&"</z></y>","//z"),B$1),"")

 

[RAJESH1960]

I didn't recognize that you are on an older version of Excel.
Try this amended formula instead:

=IFERROR(INDEX(FILTERXML("<y><z>"&SUBSTITUTE($A2,",","</z><z>")&"</z></y>","//z"),B$1),"")

Thanks for your help. Your formula too works perfectly.