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

[TABLE="width: 500"]
<tbody>[TR]
[TD]

<tbody>
[TD="align: left"] 152/C Ist FLOOR, ASAMBU ROAD, VADASERRY, NAGERCOIL,629001,TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] 21-37-L, I FLOOR, VASANTHAM MAIN ROAD, THUCKALAY,629175,TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] MAIN ROAD, BALAPALLAM POST, BALAPALLAM,629159,TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] CONCORDIA BUILDING, 245/AC K P ROAD, NAGERCOIL,629001,TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] ST JOSEPH COMPLEX 150/21 UPSTAIRS, MEENAKSHIPURAM, NAGERCOIL,629001,TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] MAIN ROAD, KANYAKUMARI,629702,TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] BLD NO.8/361,ELAMBALLUR, KUNDARA P.O., KOLLAM DISTRICT, KUNDARA,691501,KERALA [/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]




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.

[TABLE="width: 500"]
<tbody>[TR]
[TD]152/C Ist FLOOR, ASAMBU ROAD[/TD]
[TD]VADASERRY[/TD]
[TD] NAGERCOIL[/TD]
[TD]629001[/TD]
[TD]TAMIL NADU[/TD]
[/TR]
[TR]
[TD]21-37-L, I FLOOR[/TD]
[TD]VASANTHAM MAIN ROAD[/TD]
[TD]THUCKALAY[/TD]
[TD]

<tbody>
[TD="align: right"] 629175 [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] MAIN ROAD [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] BALAPALLAM POST [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] BALAPALLAM [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"] 629159 [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] CONCORDIA BUILDING, 245/AC [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] K P ROAD [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] NAGERCOIL [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"] 629001 [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] ST JOSEPH COMPLEX 150/21 UPSTAIRS [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] MEENAKSHIPURAM [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] NAGERCOIL [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"] 629001 [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]

<tbody>
[TD="align: left"] MAIN ROAD [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] KANYAKUMARI [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"] 629702 [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] TAMIL NADU [/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="align: left"] BLD NO.8/361,ELAMBALLUR, KUNDARA P.O. [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] KOLLAM DISTRICT [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] KUNDARA [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: right"] 691501 [/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="align: left"] KERALA [/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Raw Data[/TD]
[TD]Required Data[/TD]
[TD]required Data[/TD]
[TD]required Data[/TD]
[TD]required Data[/TD]
[/TR]
[TR]
[TD]152/C Ist FLOOR, ASAMBU ROAD, VADASERRY, NAGERCOIL,629001,TAMIL NADU[/TD]
[TD]152/C Ist FLOOR, ASAMBU ROAD, VADASERRY[/TD]
[TD]NAGERCOIL[/TD]
[TD]629001[/TD]
[TD]TAMIL NADU[/TD]
[/TR]
[TR]
[TD]21-37-L, I FLOOR, VASANTHAM MAIN ROAD, THUCKALAY,TAMIL NADU[/TD]
[TD]21-37-L, I FLOOR, VASANTHAM MAIN ROAD[/TD]
[TD]THUCKALAY[/TD]
[TD][/TD]
[TD]TAMIL NADU[/TD]
[/TR]
[TR]
[TD]BLD NO.8/361,ELAMBALLUR, KUNDARA P.O., KOLLAM DISTRICT, KUNDARA,691501,KERALA[/TD]
[TD]BLD NO.8/361,ELAMBALLUR, KUNDARA P.O., KOLLAM DISTRICT[/TD]
[TD]KUNDARA[/TD]
[TD]691501[/TD]
[TD]KERALA[/TD]
[/TR]
[TR]
[TD]BLD NO.8/361, 691501,KERALA[/TD]
[TD]BLD NO.8/361[/TD]
[TD][/TD]
[TD]691501[/TD]
[TD]KERALA[/TD]
[/TR]
</tbody>[/TABLE]

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