find from right?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sybmathics - 14 Feb 2008 17:36 GMT

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

Reply to this Message

Dave Peterson - 14 Feb 2008 17:55 GMT

One way:

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

> Hi all,
>
[quoted text clipped - 34 lines]
>
> Sybolt

Signature

Dave Peterson

Reply to this Message

Sybmathics - 14 Feb 2008 19:51 GMT

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

Reply to this Message

Dave Peterson - 14 Feb 2008 21:47 GMT

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 schreef:
> > One way:
[quoted text clipped - 22 lines]
>
> Sybolt

Signature

Dave Peterson

Reply to this Message

Sybmathics - 15 Feb 2008 06:35 GMT

>>> One way:
>>>
>>> =RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),LEN(A1)
>>> -LEN(SUBSTITUTE(A1,"/","")))))

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

It works fine.

Thanks a lot

Sybolt

Reply to this Message

Just Merks - 14 Feb 2008 21:11 GMT

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 all,
>
[quoted text clipped - 34 lines]
>
> Sybolt

Reply to this Message

Sybmathics - 15 Feb 2008 06:38 GMT

Just Merks schreef:

> Hello,
>
[quoted text clipped - 19 lines]
>
> 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

Reply to this Message

kounoike - 15 Feb 2008 10:06 GMT

> Just Merks schreef:
>> Hello,
[quoted text clipped - 31 lines]
>
> 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

Reply to this Message

Klemen25 - 15 Feb 2008 12:46 GMT

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?

Reply to this Message