On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Wednesday, November 2, 2016

Extract data from a long and between of the string in Excel

Extract data from a long and between of the string by using MID and SEARCH Function in Excel

One of my blog viewer Shahrukh Ali, from U.P. has raised beneath query on one of my post:

Query:
 i want only amounts which is after second (|) some time its in 3 digits and some times 4 or 5 digits  
 is there any small formula  
 Excel data is  
 774857|Fos|350|Main|Green|2234|  
 97868|Fos|4500|Main|Green|46577|  
 i mean i want only that amount which is between 2nd and 3rd (|)  
 Results should be 350 and 4500 for this data  

Solution:
Function Used:
 =MID(A2,(SEARCH("|",A2,SEARCH("|",A2,1)+1)+1),(SEARCH("|",A2,(SEARCH("|",A2,(SEARCH("|",A2,1)+1))+1))-(SEARCH("|",A2,SEARCH("|",A2,1)+1)+1)))  

Note: Type or Copy & Paste this function into Cell "B2" and string on "A2" cell








Popular Posts