Extracting Text From Right To Left - MID & FIND
 

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

Extracting Text From Right To Left - MID & FIND
 

Assuming your data is in cell A1 ...
=MID(A1,(FIND(" ",A1,(FIND("h",A1))-11))+1,(FIND("h",A1))-(FIND("
",A1,(FIND("h",A1))-11))-1)

should do it.

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"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

Extracting Text From Right To Left - MID & FIND
 

On Wed, 6 Jan 2010 08:15:01 -0800, Gary Brown
wrote:

Assuming your data is in cell A1 ...
=MID(A1,(FIND(" ",A1,(FIND("h",A1))-11))+1,(FIND("h",A1))-(FIND("
",A1,(FIND("h",A1))-11))-1)

should do it.

This formula relies on that there is only one " " within the 11
characters immediate to the left of "h". But we don't know that, do
we?

Lars-Åke

Extracting Text From Right To Left - MID & FIND
 

Lars-Åke Aspelin wrote:
On Wed, 6 Jan 2010 08:15:01 -0800, Gary Brown
wrote:

Assuming your data is in cell A1 ...
=MID(A1,(FIND(" ",A1,(FIND("h",A1))-11))+1,(FIND("h",A1))-(FIND("
",A1,(FIND("h",A1))-11))-1)

should do it.

This formula relies on that there is only one " " within the 11
characters immediate to the left of "h". But we don't know that, do
we?

Lars-Åke


Use the formula I posted on one of Brenda's other threads:


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

Will work unless the text prior to the "h" is more than 99 characters.