If these texts are placed in separate cells, e.g. like this:


A1: 9,Bhagyoday Estate, Ajod Dairy Road, Rakhial Industrial Area, Ahmedabad
-
380023 Gujarat


A2: 181, Industrial area - A, Ludhiana - 141003 Punjab


A3: Opposite Rotomac Pens, Sarkhej Bavla Highway, Village Moraiya, Ta-Sanand,
Sanand - 382213 Gujarat


A4: 41/B, Lalbaug Industrial Estate, Dr.Ambedkar Road, Lalbaug, Mumbai -
400012
Maharastra

then install this UDF:

Public Function FindRev(StrtoSearch As String, StrSearchedFor As String) As
Long
FindRev = 0
On Error Resume Next
FindRev = InStrRev(StrtoSearch, StrSearchedFor)
End Function

Formula

for address: =TRIM(LEFT(A1,FindRev(A1,",")-1))
for city:
=TRIM(MID(A1,FindRev(A1,",")+1,SEARCH("-",A1)-FindRev(A1,",")-1))
for code: =TRIM(MID(A1,SEARCH("-",A1)+2,6))
for state: =TRIM(MID(A1,SEARCH("-",A1)+8,256))


Regards,
Stefi