I would like to know if there is a way to isolate the characters to
the right of a set of characters in a string.
I know the basics on how to use the LEN, MID, RIGHT and LEFT functions
but the problem I have is that the character is a full stop and for
some of the data, the full stop occurs more than just once, otherwise
it would be quite simple.

For instance a string could be as follows: -

yyy.yyyy.yyyyyyy.yy.y.yyy.xxx

where I need to capture the xxx and the xxx could be between 2 and 5
characters long.

I have tried all sorts of ways with modified code from postings on the
Group but to no avail.

Help much appreciated.

Mark.

=SUBSTITUTE(SUBSTITUTE(A1,"y",""),".","")
or
=SUBSTITUTE(A1,"y","")
The second doesn't remvoe dots

" wrote:

I would like to know if there is a way to isolate the characters to
the right of a set of characters in a string.
I know the basics on how to use the LEN, MID, RIGHT and LEFT functions
but the problem I have is that the character is a full stop and for
some of the data, the full stop occurs more than just once, otherwise
it would be quite simple.

For instance a string could be as follows: -

yyy.yyyy.yyyyyyy.yy.y.yyy.xxx

where I need to capture the xxx and the xxx could be between 2 and 5
characters long.

I have tried all sorts of ways with modified code from postings on the
Group but to no avail.

Help much appreciated.

Mark.

One way:
This assumes that there will be a value from 2 to 5 characters after the
last period. If there isn't, then the result will be false.

=IF(MID(A1,LEN(A1)-2,1)=".",RIGHT(A1,2),IF(MID(A1,LEN(A1)-3,1)=".",RIGHT(A1,3),IF(MID(A1,LEN(A1)-4,1)=".",RIGHT(A1,4),IF(MID(A1,LEN(A1)-5,1)=".",RIGHT(A1,5)))))


HTH,
Paul


--

wrote in message
oups.com...
I would like to know if there is a way to isolate the characters to
the right of a set of characters in a string.
I know the basics on how to use the LEN, MID, RIGHT and LEFT functions
but the problem I have is that the character is a full stop and for
some of the data, the full stop occurs more than just once, otherwise
it would be quite simple.

For instance a string could be as follows: -

yyy.yyyy.yyyyyyy.yy.y.yyy.xxx

where I need to capture the xxx and the xxx could be between 2 and 5
characters long.

I have tried all sorts of ways with modified code from postings on the
Group but to no avail.

Help much appreciated.

Mark.

I assume that the "y" arn't really "y". So use this:

=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".","")))))

"Tevuna" wrote:

=SUBSTITUTE(SUBSTITUTE(A1,"y",""),".","")
or
=SUBSTITUTE(A1,"y","")
The second doesn't remvoe dots

" wrote:

I would like to know if there is a way to isolate the characters to
the right of a set of characters in a string.
I know the basics on how to use the LEN, MID, RIGHT and LEFT functions
but the problem I have is that the character is a full stop and for
some of the data, the full stop occurs more than just once, otherwise
it would be quite simple.

For instance a string could be as follows: -

yyy.yyyy.yyyyyyy.yy.y.yyy.xxx

where I need to capture the xxx and the xxx could be between 2 and 5
characters long.

I have tried all sorts of ways with modified code from postings on the
Group but to no avail.

Help much appreciated.

Mark.

On Tue, 21 Aug 2007 18:17:03 -0000, wrote:

I would like to know if there is a way to isolate the characters to
the right of a set of characters in a string.
I know the basics on how to use the LEN, MID, RIGHT and LEFT functions
but the problem I have is that the character is a full stop and for
some of the data, the full stop occurs more than just once, otherwise
it would be quite simple.

For instance a string could be as follows: -

yyy.yyyy.yyyyyyy.yy.y.yyy.xxx

where I need to capture the xxx and the xxx could be between 2 and 5
characters long.

I have tried all sorts of ways with modified code from postings on the
Group but to no avail.

Help much appreciated.

Mark.


Will "xxx" always be all of the characters following the last full stop?

If so, then:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+1,255)


--ron

On 21 Aug, 19:30, Tevuna wrote:
=SUBSTITUTE(SUBSTITUTE(A1,"y",""),".","")
or
=SUBSTITUTE(A1,"y","")
The second doesn't remvoe dots

" wrote:
I would like to know if there is a way to isolate the characters to
the right of a set of characters in a string.
I know the basics on how to use the LEN, MID, RIGHT and LEFT functions
but the problem I have is that the character is a full stop and for
some of the data, the full stop occurs more than just once, otherwise
it would be quite simple.

For instance a string could be as follows: -

yyy.yyyy.yyyyyyy.yy.y.yyy.xxx

where I need to capture the xxx and the xxx could be between 2 and 5
characters long.

I have tried all sorts of ways with modified code from postings on the
Group but to no avail.

Help much appreciated.

Mark.

Thanks, but perhaps you took me a little too literally, or more likely
I have missed the point.

The y's in my example can be any number or text, and they can be
random.
I tried substituting your Y for a wildcard but that didnt work.

here is another example: -

First Draft.Standard Subjective.Structure Proposal.evd where evd could
be any combination from 2-5 chars long

Thanks
mark

On 21 Aug, 19:41, Ron Rosenfeld wrote:
On Tue, 21 Aug 2007 18:17:03 -0000, wrote:
I would like to know if there is a way to isolate the characters to
the right of a set of characters in a string.
I know the basics on how to use the LEN, MID, RIGHT and LEFT functions
but the problem I have is that the character is a full stop and for
some of the data, the full stop occurs more than just once, otherwise
it would be quite simple.

For instance a string could be as follows: -

yyy.yyyy.yyyyyyy.yy.y.yyy.xxx

where I need to capture the xxx and the xxx could be between 2 and 5
characters long.

I have tried all sorts of ways with modified code from postings on the
Group but to no avail.

Help much appreciated.

Mark.

Will "xxx" always be all of the characters following the last full stop?

If so, then:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))+1,255)

--ron


Ron, that is just the ticket. I have run it against two sets of data
from different days and the result is spot on.
Thank you.

Thanks to everyone else as well, all responses very much appreciated.

Mark

wrote...
....
here is another example: -

First Draft.Standard Subjective.Structure Proposal.evd where evd could
be any combination from 2-5 chars long

If it's always on the right end of the string just after the last period,
then all this takes is finding the period.

=LOOKUP(2,1/(LEFT(RIGHT(x,{2;3;4;5;6}),1)="."),RIGHT(x,{1;2;3; 4;5}))

On Tue, 21 Aug 2007 18:58:23 -0000, wrote:

Ron, that is just the ticket. I have run it against two sets of data
from different days and the result is spot on.
Thank you.

Thanks to everyone else as well, all responses very much appreciated.

Mark

Glad to help. Thanks for the feedback.
--ron