Extracting text from right to left using MID and FIND

[Brenda]

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

 

[Lars-Åke Aspelin]

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

 

[Lars-Åke Aspelin]

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

 

[Mike H]

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

 

[Mike H]

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

 

[Glenn]

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))

 

[Rick Rothstein]

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.