• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Right Left formula-Need help!

Gaurang Mhatre

Gaurang Mhatre

Member
Dear Friends,

Require help in the attached file to extract state, pincode and name of the client.
I am unable to get state and name of the client

Thanks for the help in advance.

Regards,
Gaurang
 

Attachments

  • Working.xls
    26.5 KB · Views: 6
Hi Gaurang,
How would excel recognize where is Name of client, states etc? as there is no clue.
1 or more thing should be constant to get result.
 
Hi Khalid,
I forgot to mention in earlier post, actually need address from Branch address column and also state. i could extract pincode with help of right formula. Want to know formula which will extract address and state. before state is the address, that i want to extract for address. Then comes state and pincode.
is it possible?
Thanks!

Regards,
Gaurang
 
Last edited:
Just try it

For Addresses:
=LEFT(TRIM(SUBSTITUTE(A1,","," ")),FIND("qq@",SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ","qq@",LEN(TRIM(SUBSTITUTE(A1,","," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",""))-1))-1)

For State:
=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",60)),120)),SEARCH(" ",TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",60)),120)),1)-1)

Hope it works
 
Khalid NGO said:
Hi Gaurang,
Check this for Address:
=LEFT(C10,FIND(B10,C10)-1)

i tried it on C9,10 & 11
Click to expand...

Hi Khalid,
Thanks for the reply.
Just checked for pincode, i had entered Right formula bt it is not working as in some cells it is capturing 5digit numbes. so is there a formula which will capture pincode accurately.

Also the above formula which you have mentioned is working well. but since there are like 5000 address thus in some cases it is showing as #value. as the format is not uniform. but i will save the formula for future reference. Thanks alot for the help! :)

Regards,
Gaurang
 
azumi said:
Just try it

For Addresses:
=LEFT(TRIM(SUBSTITUTE(A1,","," ")),FIND("qq@",SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ","qq@",LEN(TRIM(SUBSTITUTE(A1,","," ")))-LEN(SUBSTITUTE(TRIM(SUBSTITUTE(A1,","," "))," ",""))-1))-1)

For State:
=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",60)),120)),SEARCH(" ",TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",60)),120)),1)-1)

Hope it works
Click to expand...


Hi Azumi,

It is working for state, but for address in end it is capturing few letters of state.

For eg:
4TH FLOOR RAJESWARI BUILDING 503 4'TH FLOOR SHALIMAR COMPLEX ( OPP C M I ROAD SHALIMAR COMPLEX JAIPUR

2 ex: FIRST FLOOR 176 A/5 K R AUTOMOBILE BUILDING TRIVANDRUM HIGH ROAD NEAR KODAI CAR M 7M DEALER TIRUNELVELI TAMIL


Jaipur and Tamil is getting captured in address which is state.

Appreciate your efforts for this.Thanks a lot for the help.

Regards,
Gaurang
 
You must log in or register to reply here.
Back
Top