Formula for duplicating info from one cell to another

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Timbo - 11 Apr 2008 16:52 GMT

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.

Reply to this Message

Mike H - 11 Apr 2008 17:35 GMT

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

> 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:
[quoted text clipped - 14 lines]
>
> Thanks.

Reply to this Message

Timbo - 11 Apr 2008 17:52 GMT

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.

> With the string in A1 and as shown in your example then this should work by
> extracting everything after the second space
[quoted text clipped - 21 lines]
> >
> > Thanks.

Reply to this Message

Timbo - 11 Apr 2008 19:59 GMT

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

> Hey Mike H,
>
[quoted text clipped - 27 lines]
> > >
> > > Thanks.

Reply to this Message

Timbo - 11 Apr 2008 20:56 GMT

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?

> 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
[quoted text clipped - 45 lines]
> > > >
> > > > Thanks.

Reply to this Message

Timbo - 11 Apr 2008 21:00 GMT

> 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
[quoted text clipped - 75 lines]
> > > > >
> > > > > Thanks.

Reply to this Message

Rate this thread: