Find right-most occurence of charcter

Posted by Richard S on November 12, 2001 2:13 PM

Hi all,

I know this question has been answered here somewhere before, but can't find it. I need to find the position of the right most occurence of a hyphen (-) in a text string, ie in "Co-ordina-SUPERANNUATION", return a value of 10. Find returns 3. I don't want to force a starting charcter, because in some cases ie "A&E-S&W PRODUCTIVE" I want to return 3.

TIA
Richard

Posted by faster on November 12, 2001 2:25 PM

Paste this code in a module in your workbook. You
can use it with the function wizard in User Defined
functions. I hope this helps

Function FindRightChar(MyString)
Dim i, RetVal

For i = Len(MyString) To 0 Step -1
If Mid(MyString, i, 1) = "-" Then
RetVal = i
Exit For
End If
Next i

FindRightChar = RetVal
End Function

Posted by Barrie Davidson on November 12, 2001 2:31 PM

Another option

Richard, you could also use this macro. Note that you must select your cells before running the macro. Also, using an input box, you can specify the character to search for (you're not limited to searching for "-" only).

Sub FindLastPosition()
' Written by Barrie Davidson
Dim Character As String
Dim CurrentCharacter As String

Character = InputBox("Enter character to search for")
For Each cell In Selection
counter = 0
Do Until CurrentCharacter = Character
CurrentCharacter = Mid(cell.Value, Len(cell.Value) - counter, 1)
counter = counter + 1
Loop
cell.Offset(0, 1).Value = Len(cell.Value) - counter + 1
CurrentCharacter = ""
Next cell
End Sub

Regards,
BarrieBarrie Davidson

Posted by Richard S on November 12, 2001 2:50 PM

Thx for the quick response guys (nt)

Posted by Aladin Akyurek on November 12, 2001 8:49 PM

One more... (Re: Thx for the quick response guys (nt))

Richard --

Just for "-" (or for any single char)

=SEARCH("-@-",SUBSTITUTE(A1,"-","-@-",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))

For a generic solution, put your "search" (or "look") str in B1, then use:

=SEARCH("-@-",SUBSTITUTE(A1,B1,"-@-",(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)))

provided that you use something improbable as substitution & search string ("-@-" above is supposed to have that feature).

Aladin

========

Posted by Richard S on November 12, 2001 10:22 PM

Re: One more... (Re: Thx for the quick response guys (nt))

Aladin,

I have no idea how that works, but it's just what I wanted. Didn't really want to have to run a Macro. We upload a file from a Unix system each month, and get rid of cost centres and do a pivot table on the account descriptors. I wanted it to be simple so other users could use it when I am away.

Thanks

Richard

Posted by Aladin Akyurek on November 13, 2001 5:41 AM

How it works...

Richard--

As you see from the layou-out below

=SEARCH("-@-",
SUBSTITUTE(A1,
B1,
"-@-",
(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1)))

the string arg consists of a SUBSTITUTE with 4 args. The 4th tells for which instance of B1 "-@-" must be substituted. This is a computed number that is produced by (LEN(..)-LEN(..))/LEN(..). See Huan's post how this part works. Any case it delivers frequency of occurrence of B1 in A1, a number which is the position of the "right-most" B1 in A1.Searching for "-@-" that is substituted for the last B1 in A1 gives the desired position.

Hope this explanation is intelligible.

Regards,

Aladin

========= ,

Posted by Juan Pablo on November 13, 2001 7:09 AM

"Huan" = Juan Pablo, See 5590.html (NT)

Posted by Aladin Akyurek on November 13, 2001 7:31 AM

I did that?...

My apologies Juan. -Aladin --