Thread: Extract number from cell when specific format is found
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jacob Skaria Jacob Skaria is offline
external usenet poster
  
Posts: 8,520
Default Extract number from cell when specific format is found
If the number is always after "_IE" then the below formula would work

=LEFT(MID(A1,FIND("_IE",A1)+3,99),FIND(".",MID(A1, FIND("_IE",A1)+3,99))-1)


If the number is the first numeric in the string the below should work

=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&" 0123456789")),LEN(A1)),FIND(".",MID(A1,MIN(SEARCH( {0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),LEN(A1)))-1)

If this post helps click Yes
---------------
Jacob Skaria


"paul c" wrote:

Is there a formula that will extract only the numbers that immediately follow
"E" in a string?

For example, the formula would get "1787" from this string:
http://www.glassdoor.com/Jobs/First-...10_KO11,28.htm


Reply With QuoteReply With Quote