extracting data to the right of a set character

[etradeguru]

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.

 

[Tevuna]

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

 

[PCLIVE]

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

 

[Tevuna]

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

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

 

[Ron Rosenfeld]

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

 

[etradeguru]

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

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

 

[etradeguru]

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

 

[Harlan Grove]

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

 

[Ron Rosenfeld]

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