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
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
wrote:

On Wed, 6 Jan 2010 05:55:01 -0800, 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


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" wrote in message
...
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