Hello,

I have a column that lists project, sub-project, sub-project, area and would
like the cells in this column to display only the area for a report I am
creating.

For example, cells in the column contain:
/XYZ/ABCD/New Requests
/XYZ/ABCD/Problems
/XYZ/ABCD/Features

But I only want the information beyond the last slash, for example:
New Requests
Problems
Features

Is there a function that will do this?

Thank you,
Tom

Hi Tommy

Try
=MID(A1,FIND(" ",SUBSTITUTE(A1,"/"," ",3))+1,255)
and copy down
--
Regards

Roger Govier


"tommcbrny" wrote in message
...
Hello,

I have a column that lists project, sub-project, sub-project, area and
would
like the cells in this column to display only the area for a report I
am
creating.

For example, cells in the column contain:
/XYZ/ABCD/New Requests
/XYZ/ABCD/Problems
/XYZ/ABCD/Features

But I only want the information beyond the last slash, for example:
New Requests
Problems
Features

Is there a function that will do this?

Thank you,
Tom

Hi Roger,

That almost worked. My examples didn't include spaces, however, and my real
data does. When I enter the function you provided, it eliminates only the
text befoe the first space. I tried playing with the function to account for
the spaces, but I'm only making it worse. Here is a more accurate
representation of the actual text in the column:
/XY - Z/AB - CD/New Requests
/XY - Z/AB - CD/Problems
/XY - Z/AB - CD/Features

So, space dash space between the "Y" and "Z", space dash space again between
the "B" and "C".

Can the function be adjusted to account for them?

Thank you again,
Tom
"Roger Govier" wrote:

Hi Tommy

Try
=MID(A1,FIND(" ",SUBSTITUTE(A1,"/"," ",3))+1,255)
and copy down
--
Regards

Roger Govier


"tommcbrny" wrote in message
...
Hello,

I have a column that lists project, sub-project, sub-project, area and
would
like the cells in this column to display only the area for a report I
am
creating.

For example, cells in the column contain:
/XYZ/ABCD/New Requests
/XYZ/ABCD/Problems
/XYZ/ABCD/Features

But I only want the information beyond the last slash, for example:
New Requests
Problems
Features

Is there a function that will do this?

Thank you,
Tom

Hi Tommy

AS your data didn't have spaces, I substitutes the third slash with a
space then searched for the space - hence the results you got.

Let's use the caret character " ^ " instead of space, then you should be
OK
=MID(A1,FIND(" ",SUBSTITUTE(A1,"/","^",3))+1,255)


--
Regards

Roger Govier


"tommcbrny" wrote in message
...
Hi Roger,

That almost worked. My examples didn't include spaces, however, and
my real
data does. When I enter the function you provided, it eliminates only
the
text befoe the first space. I tried playing with the function to
account for
the spaces, but I'm only making it worse. Here is a more accurate
representation of the actual text in the column:
/XY - Z/AB - CD/New Requests
/XY - Z/AB - CD/Problems
/XY - Z/AB - CD/Features

So, space dash space between the "Y" and "Z", space dash space again
between
the "B" and "C".

Can the function be adjusted to account for them?

Thank you again,
Tom
"Roger Govier" wrote:

Hi Tommy

Try
=MID(A1,FIND(" ",SUBSTITUTE(A1,"/"," ",3))+1,255)
and copy down
--
Regards

Roger Govier


"tommcbrny" wrote in message
...
Hello,

I have a column that lists project, sub-project, sub-project, area
and
would
like the cells in this column to display only the area for a report
I
am
creating.

For example, cells in the column contain:
/XYZ/ABCD/New Requests
/XYZ/ABCD/Problems
/XYZ/ABCD/Features

But I only want the information beyond the last slash, for example:
New Requests
Problems
Features

Is there a function that will do this?

Thank you,
Tom

I would copy the column then DataText to ColumnsDelimited by "/"

Then in third step pick the items to skip(do not import) and you would be left
with what you want.


Gord Dibben MS Excel MVP

On Fri, 15 Dec 2006 08:23:00 -0800, tommcbrny
wrote:

Hello,

I have a column that lists project, sub-project, sub-project, area and would
like the cells in this column to display only the area for a report I am
creating.

For example, cells in the column contain:
/XYZ/ABCD/New Requests
/XYZ/ABCD/Problems
/XYZ/ABCD/Features

But I only want the information beyond the last slash, for example:
New Requests
Problems
Features

Is there a function that will do this?

Thank you,
Tom