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

Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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. :(
Click to expand...

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
ABCD
1WIRETHHN-10-TAN-19STR-CU500S/RCU500S/R32
2WIRETHHN-10-TANTAN16
3WIRETHHN-10-TAN-19STR-CU-500S/R500S/R32
4WIRETHHN-101012
Sheet1
 
Upvote 0
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. :(
Click to expand...

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).
 
Upvote 0
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 :biggrin:
This message was edited by Russell Hauf on 2002-11-07 20:50
 
Upvote 0
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.
 
Upvote 0
Hmmmm . . .

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

Thanks
 
Upvote 0
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... :)
 
Upvote 0
EH? said:
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... :)
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Find J/N Value
Replies
1
Views
36
Fluff
Fluff
G
  • Solved
Finding previous number to sum
Replies
9
Views
114
Fluff
Fluff
S
  • Question
Macro to add sum total to track percentages
Replies
0
Views
119
spenserallen7
S
G
  • Solved
Flashfill formula from variable column
Replies
8
Views
72
Alex Blakenburg
A
G
  • Solved
Search a variable range based on cell address
Replies
4
Views
144
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,215,287
Messages
6,124,080
Members
449,140
Latest member
SheetalDixit

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top