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.

With the string in A1 and as shown in your example then this should work by
extracting everything after the second space

=MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)-1,LEN(A1))

Mike

"Timbo" wrote:

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.

Hey Mike H,

I'm not familiar with that particular use of a formula, so I have to plead
ignorance here. Your formula almost worked, but included the P after the
numerical code and before the word Science. I don't want the P.

"Mike H" wrote:

With the string in A1 and as shown in your example then this should work by
extracting everything after the second space

=MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)-1,LEN(A1))

Mike

"Timbo" wrote:

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.

I figure out how to get rid of the P (changed the -1 to a +1). However, I
have different sets of HWC#s with different amounts of digits, and some HWC#s
don't have the P. Your formula only takes into account five digit HWC#. I
have literally over 10,000 of these and I would like to automate this process
a bit so I don't have to cut and paste every time.

Here are some examples of different HWC#s:

HWC65841 P Science / Health & Medical Sciences / Human Anatomy & Physiology
/ Human Systems / Excretory (Urinary) System / Diseases & Disorders / Bladder
& Kidney Cancer

HWC9318 Science / Health & Medical Sciences / Human Anatomy & Physiology /
Human Systems / Excretory (Urinary) System

HWC9591 P Science / Health & Medical Sciences / Human Anatomy & Physiology /
Human Systems / Circulatory System / Reference / Images



"Timbo" wrote:

Hey Mike H,

I'm not familiar with that particular use of a formula, so I have to plead
ignorance here. Your formula almost worked, but included the P after the
numerical code and before the word Science. I don't want the P.

"Mike H" wrote:

With the string in A1 and as shown in your example then this should work by
extracting everything after the second space

=MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)-1,LEN(A1))

Mike

"Timbo" wrote:

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.

Actually, after having fooled with it for a while, I notice that any amount
of digits work. However, it is a string that doesn't have the P in it that
becomes a problem. For example

With you formula these two disparate digit strings work

1) HWC39758 P Science / Health & Medical Sciences / Anatomy & Physiology /
Human Systems / Immune/Lymphatic System / Immune/Lymphatic System Basics &
Overviews

Science / Health & Medical Sciences / Anatomy & Physiology / Human Systems /
Immune/Lymphatic System / Immune/Lymphatic System Basics & Overviews

2) HWC39 P Science / Health & Medical Sciences / Anatomy & Physiology /
Human Systems / Immune/Lymphatic System / Reference

Science / Health & Medical Sciences / Anatomy & Physiology / Human Systems /
Immune/Lymphatic System / Reference

However, when the P is excluded in a string, the results excludes the word
Science

HWC56331 Science / Health & Medical Sciences / Anatomy & Physiology / Human
Systems / Integumentary System

/ Health & Medical Sciences / Anatomy & Physiology / Human Systems /
Integumentary System

How do I get around this?


"Timbo" wrote:

I figure out how to get rid of the P (changed the -1 to a +1). However, I
have different sets of HWC#s with different amounts of digits, and some HWC#s
don't have the P. Your formula only takes into account five digit HWC#. I
have literally over 10,000 of these and I would like to automate this process
a bit so I don't have to cut and paste every time.

Here are some examples of different HWC#s:

HWC65841 P Science / Health & Medical Sciences / Human Anatomy & Physiology
/ Human Systems / Excretory (Urinary) System / Diseases & Disorders / Bladder
& Kidney Cancer

HWC9318 Science / Health & Medical Sciences / Human Anatomy & Physiology /
Human Systems / Excretory (Urinary) System

HWC9591 P Science / Health & Medical Sciences / Human Anatomy & Physiology /
Human Systems / Circulatory System / Reference / Images



"Timbo" wrote:

Hey Mike H,

I'm not familiar with that particular use of a formula, so I have to plead
ignorance here. Your formula almost worked, but included the P after the
numerical code and before the word Science. I don't want the P.

"Mike H" wrote:

With the string in A1 and as shown in your example then this should work by
extracting everything after the second space

=MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)-1,LEN(A1))

Mike

"Timbo" wrote:

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.

"Timbo" wrote:

Actually, after having fooled with it for a while, I notice that any amount
of digits work. However, it is a string that doesn't have the P in it that
becomes a problem. For example

With your formula these two disparate digit strings work

1) HWC39758 P Science / Health & Medical Sciences / Anatomy & Physiology /
Human Systems / Immune/Lymphatic System / Immune/Lymphatic System Basics &
Overviews

Science / Health & Medical Sciences / Anatomy & Physiology / Human Systems /
Immune/Lymphatic System / Immune/Lymphatic System Basics & Overviews

2) HWC39 P Science / Health & Medical Sciences / Anatomy & Physiology /
Human Systems / Immune/Lymphatic System / Reference

Science / Health & Medical Sciences / Anatomy & Physiology / Human Systems /
Immune/Lymphatic System / Reference

However, when the P is excluded in a string, the results excludes the word
Science

HWC56331 Science / Health & Medical Sciences / Anatomy & Physiology / Human
Systems / Integumentary System

/ Health & Medical Sciences / Anatomy & Physiology / Human Systems /
Integumentary System

How do I get around this?


"Timbo" wrote:

I figure out how to get rid of the P (changed the -1 to a +1). However, I
have different sets of HWC#s with different amounts of digits, and some HWC#s
don't have the P. Your formula only takes into account five digit HWC#. I
have literally over 10,000 of these and I would like to automate this process
a bit so I don't have to cut and paste every time.

Here are some examples of different HWC#s:

HWC65841 P Science / Health & Medical Sciences / Human Anatomy & Physiology
/ Human Systems / Excretory (Urinary) System / Diseases & Disorders / Bladder
& Kidney Cancer

HWC9318 Science / Health & Medical Sciences / Human Anatomy & Physiology /
Human Systems / Excretory (Urinary) System

HWC9591 P Science / Health & Medical Sciences / Human Anatomy & Physiology /
Human Systems / Circulatory System / Reference / Images



"Timbo" wrote:

Hey Mike H,

I'm not familiar with that particular use of a formula, so I have to plead
ignorance here. Your formula almost worked, but included the P after the
numerical code and before the word Science. I don't want the P.

"Mike H" wrote:

With the string in A1 and as shown in your example then this should work by
extracting everything after the second space

=MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)-1,LEN(A1))

Mike

"Timbo" wrote:

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.