Formula for Duplicating info from one cell to another

[Timbo]

I'd like information that I input into a cell to be automatically output into
another cell. So, for example, In one cell I input the following information:

HWC65917 P Science / Health & Medical Sciences / Human Anatomy & Physiology
/ Human Systems / Immune/Lymphatic System / Diseases & Disorders / Other
Lymphatic Disorders

In the cell next to it I'd like it to automatically output

Science / Health & Medical Sciences / Human Anatomy & Physiology / Human
Systems / Immune/Lymphatic System / Diseases & Disorders / Other Lymphatic
Disorders

by excluding the HWC65917 P.


Is this possible?

Thanks.

 

[Gary''s Student]

Very easy. with your data in A1, in B1 enter:

=SUBSTITUTE(A1,"HWC65917 P ","")

 

[Timbo]

Thanks. I'll try that.

Very easy. with your data in A1, in B1 enter:

=SUBSTITUTE(A1,"HWC65917 P ","")

 

[Timbo]

I copied your formula into B1 and all it did was duplicate all of the
information. It included the HWC65917 P info. I'd like it exclude that bit of
info.

 

[Gary''s Student]

Be sure the data in A1 has a single space before and after the P

 

[Timbo]

It does. It still just duplicates what is in A1

Be sure the data in A1 has a single space before and after the P

 

[Gary''s Student]

Try this one:

=RIGHT(A1,LEN(A1)-11)

 

[Timbo]

Thanks. That works, however, the -11 definition is restrictive in that I have
numerous other HWC#s with more or less digits, and the -11 only allows for a
5 digit number. How do I get around that? A wildcard?

Thanks again. You have been extremely helpful.

 

[Gary''s Student]

I see your point. Here is my thinking....your data looks like some id stuff
followed by a single space followed by a character followed another space.
How about if we find the SECOND space and pick up everything to the right of
that:

=RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,1)+2))

 

[Timbo]

This was close also, but no cigar. The string that doesn't have a P is still
presenting like this with the forward slash:

/ Life Sciences (Biology) / Medical Sciences / Anatomy & Physiology / Human
Systems / Reproductive System

rather than the preferred

Science / Life Sciences (Biology) / Medical Sciences / Anatomy & Physiology
/ Human Systems / Reproductive System

The others, with disparate digit amounts, work fine:

HWC9572 P Science / Life Sciences (Biology) / Medical Sciences / Anatomy &
Physiology / Human Systems / Reference / Atlases & Images

comes out as

Science / Life Sciences (Biology) / Medical Sciences / Anatomy & Physiology
/ Human Systems / Reference / Atlases & Images

and

HWC16702 P Science / Life Sciences (Biology) / Medical Sciences / Anatomy &
Physiology / Human Systems / Reference / Tutorials

comes out also as

Science / Life Sciences (Biology) / Medical Sciences / Anatomy & Physiology
/ Human Systems / Reference / Tutorials