Find function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric @ BP-EVV - 29 May 2008 13:20 GMT

If the "FIND" function tells me the first position from the LEFT that a
certain character exists in a string, what will do the same starting from the
RIGHT side of the string ?

Reply to this Message

Mike H - 29 May 2008 13:49 GMT

Hi,

I suspect I've made this more difficult than it need be but try this while
we wait for a simpler solution.

=LEN(A1)-FIND("@",SUBSTITUTE(A1,B1,"@",LEN(A1)-LEN(SUBSTITUTE(A1,B1,""))))

Text in A1 Text to find (counting from the right) in B1

or if you want to know how far from the right the first character is
=LEN(A1)-FIND(B1,A1,1)+1

Mike

> If the "FIND" function tells me the first position from the LEFT that a
> certain character exists in a string, what will do the same starting from the
> RIGHT side of the string ?

Reply to this Message

Dave Peterson - 29 May 2008 13:52 GMT

This will find the position of the last backslash in A1:

=FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))

Since =substitute() is case sensitive, you can do this to find the last a (or A)
in A1.

=FIND(CHAR(1),SUBSTITUTE(UPPER(A1),"A",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"A",""))))

> If the "FIND" function tells me the first position from the LEFT that a
> certain character exists in a string, what will do the same starting from the
> RIGHT side of the string ?

Signature

Dave Peterson

Reply to this Message

David Biddulph - 29 May 2008 13:55 GMT

=LEN(A1)+1-FIND("H",A1)
--
David Biddulph

> If the "FIND" function tells me the first position from the LEFT that a
> certain character exists in a string, what will do the same starting from
> the
> RIGHT side of the string ?

Reply to this Message

David Biddulph - 29 May 2008 13:57 GMT

But of course that isn't quite the same. It'll tell you how far from the
RIGHT the first occurrence counting from the LEFT is.
--
David Biddulph

> =LEN(A1)+1-FIND("H",A1)
> --
[quoted text clipped - 4 lines]
>> the
>> RIGHT side of the string ?

Reply to this Message

Eric @ BP-EVV - 29 May 2008 19:53 GMT

Thanks gentlemen for the great responses. Once again I have discovered that
I didn't quite give enough information to get the reply I was really hoping
for, but al lthe replies were, none the less, very good and I'm sure will
help me at some point in the future. Let me give an example of some data in
Column A and what I'm looking to determine in column B:

Column A row 1
ZSFC4347SPR SF (that's 12 spaces between the "R" and the "S",
the total length of data in col A row 1 is 25)

Column B row 1
ZSFC4347SPR

In the example above where the "SF" is there could be as many as 4
characters and as few as 0....if there are any characters in the last "x"
positions I want to be able to extract all BUT those last "x" characters into
a different column, and it is also possible that the data could look like
this:

Column A row 2
Z8814/B ADX SF (that's 11 spaces between the "X" and the "S",
the total length of data in col A row 2 is 25)

Column B row 2
Z8814/B ADX

Is that enough of a challenge for y'all ??

Again....thanks for the assistance ! I really do love these forums, they
are a great wealth of knowledge !

> But of course that isn't quite the same. It'll tell you how far from the
> RIGHT the first occurrence counting from the LEFT is.
[quoted text clipped - 9 lines]
> >> the
> >> RIGHT side of the string ?

Reply to this Message

Ron Rosenfeld - 29 May 2008 20:57 GMT

>Thanks gentlemen for the great responses. Once again I have discovered that
>I didn't quite give enough information to get the reply I was really hoping
[quoted text clipped - 26 lines]
>Again....thanks for the assistance ! I really do love these forums, they
>are a great wealth of knowledge !

Perhaps:

=TRIM(LEFT(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))

If you want to retain the trailing <space>'s, omit the TRIM function.
--ron

Reply to this Message