Can =search( ) or =find( ) work from right to left?

[Doctor Logic]

=search( ) or =find( ) work from left to right. Can I make it work from right to left?

 

[IML]

Here is the one big formula
=RIGHT(A1,LEN(A1)-FIND("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))))

Note the value error were occur if not found

 

[PaddyD]

which is just what IML's suggestion does:

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

returns

500S/R

on your example

 

[NateO]

I always try to get it all into ONE cell and (if at all possible) with a FUNCTION rather than a formula.

I was just hoping.

You could use worksheet functions with the UDF, like mid(), but we can moodify the function to give us:<pre>
Function RevInStr(findin As Range, tofind As String) As String
' Chris Rae's Orig
''Customized by Nate
Application.Volatile
If InStr(findin, tofind) = 0 Then
RevInStr = "Can't Find String"
Exit Function
End If
Dim findcha As Integer, myInt As Integer
For findcha = Len(findin) - Len(tofind) + 1 To 1 Step -1
If Mid(findin, findcha, Len(tofind)) = tofind Then
myInt = findcha
GoTo 2
End If
Next findcha
2: RevInStr = Mid(findin, myInt + 1, Len(findin))
End Function</pre>

Generating output like:

Book4
	A	B	C	D
1	WIRETHHN-10-TAN-19STR-CU500S/R	CU500S/R	32
2	WIRETHHN-10-TAN	TAN	16
3	WIRETHHN-10-TAN-19STR-CU-500S/R	500S/R	32
4	WIRETHHN-10	10	12
Sheet1

 

[Aladin Akyurek]

On 2002-11-07 18:52, Doctor Logic wrote:
Capital piece of code from Nate. (Sure wish I knew how to write VB!)

As for Paddy's example, here is the contents of a cell:

WIRE THHN-10-TAN-19STR-CU-500S/R

I would like to return everything after the last - . I can write nested serches for "-" as the silly Mr. EH? suggests except not all the cells have the same amount of "-" in them, so you nest the find for one incidence? two? five? Mr. EH? knows this so he is just being annoying. I want the formula to work on all the cells. If the formula found the FIRST incidence of "-" FROM THE RIGHT instead of the left, it would work perfect.

I suppose I could write a big formula or use multiple ajacent columns to do it but I always try to get it all into ONE cell and (if at all possible) with a FUNCTION rather than a formula.

I was just hoping.

Nothing to it...if you install the morefunc.xll add-in...

=WMID(A1,WORDCOUNT(A1,"-"),1,"-")

Otherwise...

=MID(A1,SEARCH("@",SUBSTITUTE(A1,"-","@",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,LEN(A1))

Note. Try to avoid to post a question motivated by a possible solution (although REVERSETEXT would work here).

 

[Russell Hauf]

I hate to chime in so late like this, but if you have Excel 2000 (or later), there is already a VBA function that does this: InStrRev.

So you could do something like either of these:<pre><font color='#000000'><hr align=left width=500><font color='#000080'>Public</font><font color='#000080'>Function</font> FindLast(strText<font color='#000080'>As</font> String, strFind<font color='#000080'>As</font> String)<font color='#000080'>As</font><font color='#000080'>Integer</font>
FindLast = InStrRev(strText, strFind)<font color='#000080'>End</font><font color='#000080'>Function</font><hr align=left width=500><font color='#000080'>Public</font><font color='#000080'>Function</font> GetEnd(strText<font color='#000080'>As</font> String, strReturnAfter<font color='#000080'>As</font> String)<font color='#000080'>As</font><font color='#000080'>String</font>
GetEnd = Mid(strText, InStrRev(strText, strReturnAfter) + 1)<font color='#000080'>End</font><font color='#000080'>Function</font></font></pre>


The first one returns the position of the last character you are trying to find,
and the second one returns all characters
found after the character you are trying
to find.

My 2 cents (again),

Russell
This message was edited by Russell Hauf on 2002-11-07 20:50

 

[tusharm]

You could also export the VBA InStrRev function to XL and use it as below:

<pre>
Option Explicit

Function XLInStrRev(ByVal StringCheck As String, ByVal StringMatch As String, _
Optional ByVal Start As Long = -1, _
Optional ByVal VBCompareMethod As Long = vbBinaryCompare)
XLInStrRev = InStrRev(StringCheck, StringMatch, Start, VBCompareMethod)
End Function
</pre>

Then, if C1 has the value to be searched, use =MID(C1,xlinstrrev(C1,"-")+1,LEN(C1)) to get the desired result.

For a non-UDF solution (one using an array formula), find the location of the last '-' with
=MAX(ROW(INDIRECT("1:"&LEN(C1)))*("-"=MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)))

So, to get the rest of the string after the last '-', use
=MID(C1,MAX(ROW(INDIRECT("1:"&LEN(C1)))*("-"=MID(C1,ROW(INDIRECT("1:"&LEN(C1))),1)))+1,255)

An array formula is entered with CTRL-SHIFT-ENTER rather than the usual ENTER. If done correctly, XL will display the formula within curly brackets { and }

You may find even better formulas by searching the XL newsgroups archive via google.com.

 

[Doctor Logic]

Hmmmm . . .

Reverse Text? I never heard of that one, but it sounds like it would be very helpful and also simple.

Thanks

 

[Aladin Akyurek]

On 2002-11-08 17:31, Doctor Logic wrote:
Hmmmm . . .

Reverse Text? I never heard of that one, but it sounds like it would be very helpful and also simple.

Thanks

http://216.92.17.166/board/viewtopic.php?topic=18&forum=2

I'd recommend WMID though.

 

[EH?]

To have the forumula work for all cells with in a spreadsheet you have to add in functions to count the amount of accurences of the character you are looking for, (in this case "-"). This should work for you...

=RIGHT(G3,LEN(G3)-FIND(CHAR(1),SUBSTITUTE(G3,"-",CHAR(1),LEN(G3)-LEN(SUBSTITUTE(G3,"-","")))))

No faith Dr. you just have no faith...

Thanks Paddy for sticking up for me...I appreciate it...

If you ask me this so called Dr. is a crack-pot...

 

[riteon]

To have the forumula work for all cells with in a spreadsheet you have to add in functions to count the amount of accurences of the character you are looking for, (in this case "-"). This should work for you...

=RIGHT(G3,LEN(G3)-FIND(CHAR(1),SUBSTITUTE(G3,"-",CHAR(1),LEN(G3)-LEN(SUBSTITUTE(G3,"-","")))))

No faith Dr. you just have no faith...

Thanks Paddy for sticking up for me...I appreciate it...

If you ask me this so called Dr. is a crack-pot...

Hi Eh.

This works perfectly, but I want to choose on a few characters, rather than one. how can it be changed so that rather than "-", it could be ";http://" ?

Thanks in advance