Query in excel

[Ashwini]

Hi,

9,Bhagyoday Estate, Ajod Dairy Road, Rakhial Industrial Area, Ahmedabad -
380023 Gujarat
181, Industrial area - A, Ludhiana - 141003 Punjab
Opposite Rotomac Pens, Sarkhej Bavla Highway, Village Moraiya, Ta-Sanand,
Sanand - 382213 Gujarat
41/B, Lalbaug Industrial Estate, Dr.Ambedkar Road, Lalbaug, Mumbai - 400012
Maharastra


I want to split this into Address, city, pincode & State.

Can you help me?

Regards,
Ashwini

 

[Stefi]

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

 

[Stefi]

Or - without UDF:

Formula

for address
=TRIM(LEFT(A1,FIND("*",SUBSTITUTE(A1,",","*",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1))

for city:
=TRIM(MID(A1,FIND("*",SUBSTITUTE(A1,",","*",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))+1,SEARCH("-",A1)-FIND("*",SUBSTITUTE(A1,",","*",LEN(A1)-LEN(SUBSTITUTE(A1,",",""))))-1))

Regards,
Stefi


„Stefi†ezt írta: