Find function

  • Thread starter Thread starter Eric @ BP-EVV
  • Start date Start date
E

Eric @ BP-EVV

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 ?
 
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
 
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",""))))
 
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.
 
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 !
 
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 !
Click to expand...


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
 
Back
Top