How to Extract text and numbers after or before comma

mohanprabhus

New Member
Joined
Dec 7, 2016
Messages
17
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 ROADVADASERRY NAGERCOIL629001TAMIL NADU
21-37-L, I FLOORVASANTHAM MAIN ROADTHUCKALAY
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>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,849
Office Version
  1. 365
Platform
  1. Windows
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.
 
Upvote 0

mohanprabhus

New Member
Joined
Dec 7, 2016
Messages
17
Upvote 0

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,849
Office Version
  1. 365
Platform
  1. Windows
=trim(mid(substitute(","&$a1&",",",",rept(" ",len($a1))),len($a1)*(len($a1)-len(substitute($a1,",",""))-columns($a$1:a1)+2),len($a1)))
 
Upvote 0

mohanprabhus

New Member
Joined
Dec 7, 2016
Messages
17
Hi steve,

Can you kindly helpme with this format.

Raw DataRequired Datarequired Datarequired Datarequired Data
152/C Ist FLOOR, ASAMBU ROAD, VADASERRY, NAGERCOIL,629001,TAMIL NADU152/C Ist FLOOR, ASAMBU ROAD, VADASERRYNAGERCOIL629001TAMIL NADU
21-37-L, I FLOOR, VASANTHAM MAIN ROAD, THUCKALAY,TAMIL NADU21-37-L, I FLOOR, VASANTHAM MAIN ROADTHUCKALAYTAMIL NADU
BLD NO.8/361,ELAMBALLUR, KUNDARA P.O., KOLLAM DISTRICT, KUNDARA,691501,KERALABLD NO.8/361,ELAMBALLUR, KUNDARA P.O., KOLLAM DISTRICTKUNDARA691501KERALA
BLD NO.8/361, 691501,KERALABLD NO.8/361691501KERALA

<tbody>
</tbody>



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

Forum statistics

Threads
1,191,173
Messages
5,985,097
Members
439,940
Latest member
Kyrad42

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top