Excel - find from right?

Asked By Sybmathics on 14-Feb-08 12:36 PM
Hi all,


I am searching for a solution to find the position of a character I from
the right.

the problem is described as:


read the text after the second /

so if you have:

as/1200/gh
hongkong/4/as
johannesburg/15000/dr

I want excel to return

gh
as
dr

This would be easy if you could find for the position of the / from the
right, but the find function reads from the left.

I also want to be able to return the last characterset when it is more
than 2 characters long, so from

amsterdam/2500/pill

I want to return

pill

I hope you understand my problem and appreciate any suggestion.

Greets,

Sybolt




Dave Peterson replied on 14-Feb-08 12:55 PM
One way:

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




--

Dave Peterson
Sybmathics replied on 14-Feb-08 02:51 PM
Dave Peterson schreef:
Dave,
thanks for your quick response,

I also need to know (I understand I wasn't complete)
if i can subtract the last characters from a string like

nowhere/1200/go/ds

where there are three /'s

man, this really kills me.

Also keep in mind, sometimes the /'s are like \'s
So the text-to-columns function won't work either.

Is there a way, you think?

greets,

Sybolt
Just Merks replied on 14-Feb-08 04:11 PM
Hello,

Write your own function macro. Might be done smarter, but it works pretty
fast.

ie:

=countit(A1)     (where A1 is your string.)

in visual basic

Function countit(lasttext)
a = Len(lasttext)
For n = 1 To a
m = Mid(lasttext, n, 1)
If m = "/" Then dash = n
Next
countit = Mid(lasttext, dash + 1, a - dash)
End Function

regards,

Just
Dave Peterson replied on 14-Feb-08 04:47 PM
This formula gets the piece after the last slash--no matter how many slashes you
use.

If I were doing it, I would change all the backslashes to slashes--just use:
Select the column
Edit|replace
what: \
with: /
replace all

Then I could use the formula or data|Text to columns.


--

Dave Peterson
Sybmathics replied on 15-Feb-08 01:35 AM
I suppose you had this strange mixture in one of your drawers?

It works fine.

Thanks a lot

Sybolt
Sybmathics replied on 15-Feb-08 01:38 AM
Just Merks schreef:

Hi Just,

I was thnking of writing my own function.
Do you know how to create an optional argument in a udf.
Is it possible to gray-out the optional argument in a udf (like
range_lookup in vlookup)

greets

Sybolt
kounoike replied on 15-Feb-08 05:06 AM
if you are thinking of udf, then try this one.
ussage of this function is like this
= mysplit(A1) or = mysplit(A1,,1) or  = mysplit(A1,"\") , etc

Function mysplit(str As String, Optional delimiter As String = "/", Optional
num As Long = -1)
Dim tmp
tmp = Split(str, delimiter)
If num > UBound(tmp) Or num < -1 Then
mysplit = CVErr(xlErrNum)
Exit Function
ElseIf UBound(tmp) = 0 Then
mysplit = CVErr(xlErrNA)
Exit Function
ElseIf num = -1 Then
num = UBound(tmp)
End If
mysplit = tmp(num)
End Function

keiji
Klemen25 replied on 16-Feb-08 08:46 PM
If this texts
as/1200/gh
hongkong/4/as
johannesburg/15000/dr

are in one cell- wouldn't it be easier just to use data- text to
columns- delimited- special- /.
In this way you get all the text after the last / in separate cell.
If you need to combine the other columns again use formula =A1&B1.

Simpler?
E Excel replied to Dave Peterson on 09-Dec-10 01:38 PM
I came across the formula you wrote and it really helped me. Thanks!