Extract some data from a cell to another

[Hatim]

Hello there..


I have few hundred cells contains postal address in each cell eg:


Cell A1

Fahim Shah

B1 909 Sobha Aquamarine

Sarjapur Outer Ring Road

Bangalore - 560103

KA


CellA2

Annu Singh

206 207,Covalense Technologies,

Aaditya Trade Center,

Ameerpet

Hyderabad - 500038

AP


I need to extract the city name and State to other cell eg:


Cell B1

Bangalore - KA


Cell B2

Hyderabad - AP


Can anyone help me create a simple formula that would ease my job and I would not have to type it manually. Many Thanks.

 

[bobhc]

Good day Hatim


Select all the cells in column A and do a text to columns then delete the columns not needed.

 

[Hatim]

Hello Bobhc...


Thanks but it still a tedious process. Formula would work best since the address column keeps adding up everyday. If I have a formula I can copy down to the other colum and it would just extract automatically.


In my previous someone helped me with a formula to extract pincode from an address cell.

 

[shrivallabha]

Hatim,


With sample posted above following formula works but it will always be tricky.


=SUBSTITUTE(MID(A1,FIND("~",SUBSTITUTE(A1,CHAR(10),"~",LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))-1),1),100),MID(A1,FIND("-",A1,1),8),"")

 

[Hatim]

Hi Shrivallabha,


Thanks, I tried the formula with the address in A1 but unfortunately its not working. I do not know Excel Much so I cant read formula as well, thus I wouldn't know if its tricky..


the result shows #VALUE!

 

[shrivallabha]

I'd posted after testing. See attached file.

https://www.box.com/s/24sybcadzkcqzc56eke1

 

[Hatim]

I am using excel for mac 2011.. it shows #VALUE!.. but your online link is showing the proper result.. does version makes a difference?

 

[shrivallabha]

That is definitely affecting the formula. Unfortunately, I do not have Mac.


Do you know "Evaluate Formula" option.

http://www.youtube.com/watch?v=-yWoLbvyMoA


Check the part of the formula which gives error.