Hi all, I have an excel data file. In that file there are two columns I need to use and one column of it is irregular. As you guess I need to extract some numerical variables from that irregular contained column. For example I need to find the zipcodes which starts with "60". I have managed to write the formula of it in excel which is "=IFERROR(IF(AND(LEN(TRIM(SUBSTITUTE(MID(K2,FIND(TEXT($L$1,0),K2,1)-1,1)&MID(K2,FIND(TEXT($L$1,0),K2,1)+5,1),CHAR(160),"")))=0,MID(K2,FIND(TEXT($L$1,0),K2,1),2)<>RIGHT(K2,2)),$L$1,"X"),"X")" *Zip codes are 5 digits. What does this formula do? : It finds the starting position of desired 2 digit then 1- Check if the letter before that 2 zip codes is empty and 2- Check if the 3 letter after that 2 zip code is empty and 3- Check if there is any letter after 2 zip code just in case those digits are the last letter of that cell. You can see some lines of the source excel file. If you are not able to download it I added some lines as well. Irregular Column Desired zip codes Starting with a-haUKxUt 60000 WawawxwK tIhwahwTw 60 60 KxIwaww 66000 yawtIh yInbaTwTw 66-60 60 wtwwwxhh 00666 WaIwxw twIawahwwTw 6 60 wUwt Vth 060006 00000 *** *** 60 awaKx 06060 IhtttwTxwT tawtttIhtaw wTw 666 60 KxIwaww 60066 yawtIh YtwnbKwTw 00 60 wUwt Vth 660666 00000 *** *** 60 wUwt Vth 600660 00000 *** *** 60 awaKx twUyta 00666 yxw atwTaw yxtawwTw 60 60 awaKx twtwwaw 06600 xUtwyUwt ytyIhtaw wTw 66 60 TatUT-hxy+tUT 00006 wUaytyxUwah/TyUawIh txhtahwxtnbxaw tx 60 Iwwx-wxwKT 66660 ttwahyUwt yttyawtatwaw wTw 600 60 Any help or idea is appreciated. Thanks in advance, have a nice day.
... View more