First of all I would like to thank you in advance for taking the time to look 
at this post and providing any help possible.  I am a novice trying to learn 
Excel and find it to be "fun".

I am trying to extract text from a string for which the character length is 
never the same.  I have found a tutorial on how one may extract a middle name 
but that has confused me in since it is set up to read from left to right.  
What I need to do is find a lower case "h" and take all of the text to the 
left of the "h" up to but not including the first space encouontered reading 
from right to left.  Here is a sample of what is in cell A1 for instance:

SIMS.22005800.005AA 010 VISUALLY INSPECT THE MCC-XXXXXX B-XXXX MCC X 1 , I 
E>.P3.E 0.50h 0.1 01/06/10 06:30AM 01/29/10 12:00AM

I am looking to grab just the text 0.50 from the above example using the 
lower case "h" as an anchor point to start from and move to the left to find 
the first space before the 0 in the text 0.50h, stop and then take everything 
between the space and the "h".  The lower case "h" is unique as the E to the 
left of 0.50 is not and changes.  The lower case "h" always is constant.

I am stumped on this one and would sincerely appreciate any input from any 
of you experts out there.

Thanks!

Brenda   

On Wed, 6 Jan 2010 05:55:01 -0800, Brenda
<Brenda@discussions.microsoft.com> wrote:

>First of all I would like to thank you in advance for taking the time to look 
>at this post and providing any help possible.  I am a novice trying to learn 
>Excel and find it to be "fun".
>
>I am trying to extract text from a string for which the character length is 
>never the same.  I have found a tutorial on how one may extract a middle name 
>but that has confused me in since it is set up to read from left to right.  
>What I need to do is find a lower case "h" and take all of the text to the 
>left of the "h" up to but not including the first space encouontered reading 
>from right to left.  Here is a sample of what is in cell A1 for instance:
>
>SIMS.22005800.005AA 010 VISUALLY INSPECT THE MCC-XXXXXX B-XXXX MCC X 1 , I 
>E>.P3.E 0.50h 0.1 01/06/10 06:30AM 01/29/10 12:00AM
>
>I am looking to grab just the text 0.50 from the above example using the 
>lower case "h" as an anchor point to start from and move to the left to find 
>the first space before the 0 in the text 0.50h, stop and then take everything 
>between the space and the "h".  The lower case "h" is unique as the E to the 
>left of 0.50 is not and changes.  The lower case "h" always is constant.
>
>I am stumped on this one and would sincerely appreciate any input from any 
>of you experts out there.
>
>Thanks!
>
>Brenda   


Try this formula:

=MID(A1,MAX((MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1)=
" ")*ROW(OFFSET(A1,,,LEN(A1)))*(ROW(OFFSET(A1,,,LEN(A1)))
<FIND("h",A1)))+1,FIND("h",A1)-MAX((MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1)=
" ")*ROW(OFFSET(A1,,,LEN(A1)))*(ROW(OFFSET(A1,,,LEN(A1)))
<FIND("h",A1))))

Note: Thisi is an array formula that should be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-�ke

On Wed, 06 Jan 2010 15:05:51 +0100, Lars-�ke Aspelin
<larske@REMOOOVE.telia.com> wrote:

>On Wed, 6 Jan 2010 05:55:01 -0800, Brenda
><Brenda@discussions.microsoft.com> wrote:
>
>>First of all I would like to thank you in advance for taking the time to look 
>>at this post and providing any help possible.  I am a novice trying to learn 
>>Excel and find it to be "fun".
>>
>>I am trying to extract text from a string for which the character length is 
>>never the same.  I have found a tutorial on how one may extract a middle name 
>>but that has confused me in since it is set up to read from left to right.  
>>What I need to do is find a lower case "h" and take all of the text to the 
>>left of the "h" up to but not including the first space encouontered reading 
>>from right to left.  Here is a sample of what is in cell A1 for instance:
>>
>>SIMS.22005800.005AA 010 VISUALLY INSPECT THE MCC-XXXXXX B-XXXX MCC X 1 , I 
>>E>.P3.E 0.50h 0.1 01/06/10 06:30AM 01/29/10 12:00AM
>>
>>I am looking to grab just the text 0.50 from the above example using the 
>>lower case "h" as an anchor point to start from and move to the left to find 
>>the first space before the 0 in the text 0.50h, stop and then take everything 
>>between the space and the "h".  The lower case "h" is unique as the E to the 
>>left of 0.50 is not and changes.  The lower case "h" always is constant.
>>
>>I am stumped on this one and would sincerely appreciate any input from any 
>>of you experts out there.
>>
>>Thanks!
>>
>>Brenda   
>
>
>Try this formula:
>
>=MID(A1,MAX((MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1)=
>" ")*ROW(OFFSET(A1,,,LEN(A1)))*(ROW(OFFSET(A1,,,LEN(A1)))
><FIND("h",A1)))+1,FIND("h",A1)-MAX((MID(A1,ROW(OFFSET(A1,,,LEN(A1))),1)=
>" ")*ROW(OFFSET(A1,,,LEN(A1)))*(ROW(OFFSET(A1,,,LEN(A1)))
><FIND("h",A1))))
>
>Note: Thisi is an array formula that should be confirmed by
>CTRL+SHIFT+ENTER rather than just ENTER.
>
>Hope this helps / Lars-�ke

Here is a shorter formula

=RIGHT(LEFT(A1,FIND("h",A1)),LEN(LEFT(A1,FIND("h",A1)))-
MAX((MID(LEFT(A1,FIND("h",A1)),ROW(OFFSET(A1,,,LEN(A1))),1)
=" ")*ROW(OFFSET(A1,,,LEN(A1)))))

Note: Thisi is an array formula that should be confirmed by
CTRL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-�ke

Brenda,

The E is unique only if we test from the right but it would have been better 
to provide more examples to prove this. On the ass umption it is then this 
UDF should work

ALT+F11 to open vb editor, Right click 'ThisWorkbook' and 'Insert Module' 
and paste the code below in

Call with =FindNum(A1)
assuming your string is in A1

Function FindNum(srchstring As String)
theE = InStrRev(srchstring, "e", , vbTextCompare)
theSpace = InStr(theE + 2, srchstring, " ", vbTextCompare)
FindNum = Mid(srchstring, theE + 1, (theSpace - theE) - 2) + 0
End Function

Mike

"Brenda" wrote:

> First of all I would like to thank you in advance for taking the time to look 
> at this post and providing any help possible.  I am a novice trying to learn 
> Excel and find it to be "fun".
> 
> I am trying to extract text from a string for which the character length is 
> never the same.  I have found a tutorial on how one may extract a middle name 
> but that has confused me in since it is set up to read from left to right.  
> What I need to do is find a lower case "h" and take all of the text to the 
> left of the "h" up to but not including the first space encouontered reading 
> from right to left.  Here is a sample of what is in cell A1 for instance:
> 
> SIMS.22005800.005AA 010 VISUALLY INSPECT THE MCC-XXXXXX B-XXXX MCC X 1 , I 
> E>.P3.E 0.50h 0.1 01/06/10 06:30AM 01/29/10 12:00AM
> 
> I am looking to grab just the text 0.50 from the above example using the 
> lower case "h" as an anchor point to start from and move to the left to find 
> the first space before the 0 in the text 0.50h, stop and then take everything 
> between the space and the "h".  The lower case "h" is unique as the E to the 
> left of 0.50 is not and changes.  The lower case "h" always is constant.
> 
> I am stumped on this one and would sincerely appreciate any input from any 
> of you experts out there.
> 
> Thanks!
> 
> Brenda   
> 

I think i'm now reading this that the E can change so try this

Function FindNum(SrchString As String)
theh = InStrRev(SrchString, "h", , vbTextCompare)
thespace = InStrRev(SrchString, " ", theh, vbTextCompare)
FindNum = Trim(Mid(SrchString, thespace, theh - thespace)) + 0
End Function

Mike

"Mike H" wrote:

> Brenda,
> 
> The E is unique only if we test from the right but it would have been better 
> to provide more examples to prove this. On the ass umption it is then this 
> UDF should work
> 
> ALT+F11 to open vb editor, Right click 'ThisWorkbook' and 'Insert Module' 
> and paste the code below in
> 
> Call with =FindNum(A1)
> assuming your string is in A1
> 
> Function FindNum(srchstring As String)
> theE = InStrRev(srchstring, "e", , vbTextCompare)
> theSpace = InStr(theE + 2, srchstring, " ", vbTextCompare)
> FindNum = Mid(srchstring, theE + 1, (theSpace - theE) - 2) + 0
> End Function
> 
> Mike
> 
> "Brenda" wrote:
> 
> > First of all I would like to thank you in advance for taking the time to look 
> > at this post and providing any help possible.  I am a novice trying to learn 
> > Excel and find it to be "fun".
> > 
> > I am trying to extract text from a string for which the character length is 
> > never the same.  I have found a tutorial on how one may extract a middle name 
> > but that has confused me in since it is set up to read from left to right.  
> > What I need to do is find a lower case "h" and take all of the text to the 
> > left of the "h" up to but not including the first space encouontered reading 
> > from right to left.  Here is a sample of what is in cell A1 for instance:
> > 
> > SIMS.22005800.005AA 010 VISUALLY INSPECT THE MCC-XXXXXX B-XXXX MCC X 1 , I 
> > E>.P3.E 0.50h 0.1 01/06/10 06:30AM 01/29/10 12:00AM
> > 
> > I am looking to grab just the text 0.50 from the above example using the 
> > lower case "h" as an anchor point to start from and move to the left to find 
> > the first space before the 0 in the text 0.50h, stop and then take everything 
> > between the space and the "h".  The lower case "h" is unique as the E to the 
> > left of 0.50 is not and changes.  The lower case "h" always is constant.
> > 
> > I am stumped on this one and would sincerely appreciate any input from any 
> > of you experts out there.
> > 
> > Thanks!
> > 
> > Brenda   
> > 

Brenda wrote:
> First of all I would like to thank you in advance for taking the time to look 
> at this post and providing any help possible.  I am a novice trying to learn 
> Excel and find it to be "fun".
> 
> I am trying to extract text from a string for which the character length is 
> never the same.  I have found a tutorial on how one may extract a middle name 
> but that has confused me in since it is set up to read from left to right.  
> What I need to do is find a lower case "h" and take all of the text to the 
> left of the "h" up to but not including the first space encouontered reading 
> from right to left.  Here is a sample of what is in cell A1 for instance:
> 
> SIMS.22005800.005AA 010 VISUALLY INSPECT THE MCC-XXXXXX B-XXXX MCC X 1 , I 
> E>.P3.E 0.50h 0.1 01/06/10 06:30AM 01/29/10 12:00AM
> 
> I am looking to grab just the text 0.50 from the above example using the 
> lower case "h" as an anchor point to start from and move to the left to find 
> the first space before the 0 in the text 0.50h, stop and then take everything 
> between the space and the "h".  The lower case "h" is unique as the E to the 
> left of 0.50 is not and changes.  The lower case "h" always is constant.
> 
> I am stumped on this one and would sincerely appreciate any input from any 
> of you experts out there.
> 
> Thanks!
> 
> Brenda   
> 

NOT an array formula:

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),
FIND("h",SUBSTITUTE(A1," ",REPT(" ",99)))-99,99))

Give this one a try...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("h",A1)-1)," ",REPT(" ",99)),99))

If there can be more than 99 characters in front of the "h", then change 
both 99s to 999.

-- 
Rick (MVP - Excel)


"Brenda" <Brenda@discussions.microsoft.com> wrote in message 
news:2B923810-7EC2-4E5D-BCCB-3185F6F63704@microsoft.com...
> First of all I would like to thank you in advance for taking the time to 
> look
> at this post and providing any help possible.  I am a novice trying to 
> learn
> Excel and find it to be "fun".
>
> I am trying to extract text from a string for which the character length 
> is
> never the same.  I have found a tutorial on how one may extract a middle 
> name
> but that has confused me in since it is set up to read from left to right.
> What I need to do is find a lower case "h" and take all of the text to the
> left of the "h" up to but not including the first space encouontered 
> reading
> from right to left.  Here is a sample of what is in cell A1 for instance:
>
> SIMS.22005800.005AA 010 VISUALLY INSPECT THE MCC-XXXXXX B-XXXX MCC X 1 , I
> E>.P3.E 0.50h 0.1 01/06/10 06:30AM 01/29/10 12:00AM
>
> I am looking to grab just the text 0.50 from the above example using the
> lower case "h" as an anchor point to start from and move to the left to 
> find
> the first space before the 0 in the text 0.50h, stop and then take 
> everything
> between the space and the "h".  The lower case "h" is unique as the E to 
> the
> left of 0.50 is not and changes.  The lower case "h" always is constant.
>
> I am stumped on this one and would sincerely appreciate any input from any
> of you experts out there.
>
> Thanks!
>
> Brenda
>