How to Extract text and numbers after or before comma

[mohanprabhus]

Hi All,

I have my text data as below mentioned & i need to extract data

152/C Ist FLOOR, ASAMBU ROAD, VADASERRY, NAGERCOIL,629001,TAMIL NADU <tbody> </tbody>

21-37-L, I FLOOR, VASANTHAM MAIN ROAD, THUCKALAY,629175,TAMIL NADU <tbody> </tbody>

MAIN ROAD, BALAPALLAM POST, BALAPALLAM,629159,TAMIL NADU <tbody> </tbody>

CONCORDIA BUILDING, 245/AC K P ROAD, NAGERCOIL,629001,TAMIL NADU <tbody> </tbody>

ST JOSEPH COMPLEX 150/21 UPSTAIRS, MEENAKSHIPURAM, NAGERCOIL,629001,TAMIL NADU <tbody> </tbody>

MAIN ROAD, KANYAKUMARI,629702,TAMIL NADU <tbody> </tbody>

BLD NO.8/361,ELAMBALLUR, KUNDARA P.O., KOLLAM DISTRICT, KUNDARA,691501,KERALA <tbody> </tbody>

<tbody>
</tbody>

but i want like this, Can you helpme with this format And provide the formula to update.

I have extracted last one by using formula (=MID(H2,FIND("|",SUBSTITUTE(H2,",","|",LEN(H2)-LEN(SUBSTITUTE(H2,",",""))))+1,500) ), but do no to extract in between text and numbers.

152/C Ist FLOOR, ASAMBU ROAD	VADASERRY	NAGERCOIL	629001	TAMIL NADU
21-37-L, I FLOOR	VASANTHAM MAIN ROAD	THUCKALAY	629175 <tbody> </tbody>	TAMIL NADU <tbody> </tbody>
MAIN ROAD <tbody> </tbody>	BALAPALLAM POST <tbody> </tbody>	BALAPALLAM <tbody> </tbody>	629159 <tbody> </tbody>	TAMIL NADU <tbody> </tbody>
CONCORDIA BUILDING, 245/AC <tbody> </tbody>	K P ROAD <tbody> </tbody>	NAGERCOIL <tbody> </tbody>	629001 <tbody> </tbody>	TAMIL NADU <tbody> </tbody>
ST JOSEPH COMPLEX 150/21 UPSTAIRS <tbody> </tbody>	MEENAKSHIPURAM <tbody> </tbody>	NAGERCOIL <tbody> </tbody>	629001 <tbody> </tbody>	TAMIL NADU <tbody> </tbody>
	MAIN ROAD <tbody> </tbody>	KANYAKUMARI <tbody> </tbody>	629702 <tbody> </tbody>	TAMIL NADU <tbody> </tbody>
BLD NO.8/361,ELAMBALLUR, KUNDARA P.O. <tbody> </tbody>	KOLLAM DISTRICT <tbody> </tbody>	KUNDARA <tbody> </tbody>	691501 <tbody> </tbody>	KERALA <tbody> </tbody>

<tbody>
</tbody>

 

[steve the fish]

Try this maybe (put address in A1):

=TRIM(MID(SUBSTITUTE(","&$A1&",",",",REPT(" ",LEN($A1))),LEN($A1)*COLUMNS($A$1:A1),LEN($A1)))

Copy across until all the address shows.

 

[mohanprabhus]

Thank you so much Steve, ( this is wonderful & awesome)

Can you kindly helpme with the same formula, i need the text from right to left

Try this maybe (put address in A1):

=TRIM(MID(SUBSTITUTE(","&$A1&",",",",REPT(" ",LEN($A1))),LEN($A1)*COLUMNS($A$1:A1),LEN($A1)))

Copy across until all the address shows.

http://www.mrexcel.com/forum/excel-...t-numbers-after-before-comma.html#post4699836

 

[steve the fish]

=trim(mid(substitute(","&$a1&",",",",rept(" ",len($a1))),len($a1)*(len($a1)-len(substitute($a1,",",""))-columns($a$1:a1)+2),len($a1)))

 

[mohanprabhus]

Thank you so much Steve, This solution solved my Months Works

=trim(mid(substitute(","&$a1&",",",",rept(" ",len($a1))),len($a1)*(len($a1)-len(substitute($a1,",",""))-columns($a$1:a1)+2),len($a1)))

 

[mohanprabhus]

Hi steve,

Can you kindly helpme with this format.

Raw Data	Required Data	required Data	required Data	required Data
152/C Ist FLOOR, ASAMBU ROAD, VADASERRY, NAGERCOIL,629001,TAMIL NADU	152/C Ist FLOOR, ASAMBU ROAD, VADASERRY	NAGERCOIL	629001	TAMIL NADU
21-37-L, I FLOOR, VASANTHAM MAIN ROAD, THUCKALAY,TAMIL NADU	21-37-L, I FLOOR, VASANTHAM MAIN ROAD	THUCKALAY		TAMIL NADU
BLD NO.8/361,ELAMBALLUR, KUNDARA P.O., KOLLAM DISTRICT, KUNDARA,691501,KERALA	BLD NO.8/361,ELAMBALLUR, KUNDARA P.O., KOLLAM DISTRICT	KUNDARA	691501	KERALA
BLD NO.8/361, 691501,KERALA	BLD NO.8/361		691501	KERALA

<tbody>
</tbody>

=trim(mid(substitute(","&$a1&",",",",rept(" ",len($a1))),len($a1)*(len($a1)-len(substitute($a1,",",""))-columns($a$1:a1)+2),len($a1)))