find from right?

[Sybmathics]

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]

One way:

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

 

[Sybmathics]

Dave Peterson schreef:

One way:

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

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]

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]

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.

 

[Sybmathics]

I suppose you had this strange mixture in one of your drawers?

It works fine.

Thanks a lot

Sybolt

 

[Sybmathics]

Just Merks schreef:

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

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]

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

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]

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?