Search / Mid function
 

Hi, I want to return the words/ string of words after the last divider "/".
e.g. in Column A, there are
Society/Education/University/USA
Society/Education/University/USA/University of Chicago
Family/babies
Leisure/Outdoor/camping

I want to return:
USA
University of Chicago
babies
camping
in Column B.

The number of "/" is variable in each row.

I only know how to return the value after a specific count of "/", like:
=mid(a1, search("/", A1, 2), Len(A1)) to return University/USA
But I don't know how to return the value counting from the last "/".
Can anyone help me?

Thanks!

Search / Mid function
 

Try this:

=RIGHT(A1,LEN(A1)-SEARCH("^^^",SUBSTITUTE(A1,"/","^^^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Landa" wrote in message
...
Hi, I want to return the words/ string of words after the last divider
"/".
e.g. in Column A, there are
Society/Education/University/USA
Society/Education/University/USA/University of Chicago
Family/babies
Leisure/Outdoor/camping

I want to return:
USA
University of Chicago
babies
camping
in Column B.

The number of "/" is variable in each row.

I only know how to return the value after a specific count of "/", like:
=mid(a1, search("/", A1, 2), Len(A1)) to return University/USA
But I don't know how to return the value counting from the last "/".
Can anyone help me?

Thanks!

Search / Mid function
 

Assuming that A2 contains the text string, try...

=REPLACE(A2,1,FIND("^^",SUBSTITUTE(A2,"/","^^",LEN(A2)-LEN(SUBSTITUTE(A2,
"/","")))),"")

Hope this helps!

In article ,
Landa wrote:

Hi, I want to return the words/ string of words after the last divider "/".
e.g. in Column A, there are
Society/Education/University/USA
Society/Education/University/USA/University of Chicago
Family/babies
Leisure/Outdoor/camping

I want to return:
USA
University of Chicago
babies
camping
in Column B.

The number of "/" is variable in each row.

I only know how to return the value after a specific count of "/", like:
=mid(a1, search("/", A1, 2), Len(A1)) to return University/USA
But I don't know how to return the value counting from the last "/".
Can anyone help me?

Thanks!

Search / Mid function
 

Thank you very much RagDyer and Domenic. Both of your suggestions works!!:)

"Domenic" wrote:

Assuming that A2 contains the text string, try...

=REPLACE(A2,1,FIND("^^",SUBSTITUTE(A2,"/","^^",LEN(A2)-LEN(SUBSTITUTE(A2,
"/","")))),"")

Hope this helps!

In article ,
Landa wrote:

Hi, I want to return the words/ string of words after the last divider "/".
e.g. in Column A, there are
Society/Education/University/USA
Society/Education/University/USA/University of Chicago
Family/babies
Leisure/Outdoor/camping

I want to return:
USA
University of Chicago
babies
camping
in Column B.

The number of "/" is variable in each row.

I only know how to return the value after a specific count of "/", like:
=mid(a1, search("/", A1, 2), Len(A1)) to return University/USA
But I don't know how to return the value counting from the last "/".
Can anyone help me?

Thanks!

Search / Mid function
 

And we thank you for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Landa" wrote in message
...
Thank you very much RagDyer and Domenic. Both of your suggestions
works!!:)

"Domenic" wrote:

Assuming that A2 contains the text string, try...


=REPLACE(A2,1,FIND("^^",SUBSTITUTE(A2,"/","^^",LEN(A2)-LEN(SUBSTITUTE(A2,
"/","")))),"")

Hope this helps!

In article ,
Landa wrote:

Hi, I want to return the words/ string of words after the last divider
"/".
e.g. in Column A, there are
Society/Education/University/USA
Society/Education/University/USA/University of Chicago
Family/babies
Leisure/Outdoor/camping

I want to return:
USA
University of Chicago
babies
camping
in Column B.

The number of "/" is variable in each row.

I only know how to return the value after a specific count of "/",
like:
=mid(a1, search("/", A1, 2), Len(A1)) to return University/USA
But I don't know how to return the value counting from the last "/".
Can anyone help me?

Thanks!