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

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

On Fri, 31 Jul 2009 11:22:01 -0700, paul c <paul
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


Do you want only the digits? Or could the number include the decimal -- e.g.
should it be 1787.0 in this instance?

In either event, this can be done easily with a User Defined Function. The one
below is annotated so you can set it up for either option. And it will return
a #NUM! error if there are no digits after any "E".


To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=NumAfterE(cell_ref

in some cell.

==================================
Option Explicit
Function NumAfterE(s As String) As Variant
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = "E(\d+(\.\d+)?)"
'for only digits and no decimals, change to
're.Pattern = "E(\d+)"
If re.test(s) = True Then
Set mc = re.Execute(s)
NumAfterE = CDbl(mc(0).submatches(0))
Else: NumAfterE = CVErr(xlErrNum)
End If
End Function
==================================
--ron

Ron Rosenfeld wrote...
paul c <paul wrote...
Is there a formula that will extract only the numbers that immediately follow
"E" in a string?
....
In either event, this can be done easily with a User Defined Function. . .