Continuation: Formula for Duplicating info from one cell to anothe
I posted this on Friday with several helpful hints, but I was out for the
weekend, so I'll post this string again. I haven't quite found my answer.
Hopefully, I can get one:
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.
Was this post helpful to you?
Reply | Print post TopTop
Gary''s Student 4/11/2008 8:54 AM PST
Very easy. with your data in A1, in B1 enter:
=SUBSTITUTE(A1,"HWC65917 P ","")
--
Gary''s Student - gsnu200778
"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.
Did this post answer the question?
Reply | Print post TopTop
Timbo 4/11/2008 9:00 AM PST
Thanks. I'll try that.
"Gary''s Student" wrote:
Very easy. with your data in A1, in B1 enter:
=SUBSTITUTE(A1,"HWC65917 P ","")
--
Gary''s Student - gsnu200778
"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.
Was this post helpful to you?
Reply | Print post TopTop
Timbo 4/11/2008 9:08 AM PST
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.
"Timbo" wrote:
Thanks. I'll try that.
"Gary''s Student" wrote:
Very easy. with your data in A1, in B1 enter:
=SUBSTITUTE(A1,"HWC65917 P ","")
--
Gary''s Student - gsnu200778
"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.
Was this post helpful to you?
Reply | Print post TopTop
Gary''s Student 4/11/2008 9:31 AM PST
Be sure the data in A1 has a single space before and after the P
--
Gary''s Student - gsnu200778
"Timbo" wrote:
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.
"Timbo" wrote:
Thanks. I'll try that.
"Gary''s Student" wrote:
Very easy. with your data in A1, in B1 enter:
=SUBSTITUTE(A1,"HWC65917 P ","")
--
Gary''s Student - gsnu200778
"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.
Did this post answer the question?
Reply | Print post TopTop
Timbo 4/11/2008 9:46 AM PST
It does. It still just duplicates what is in A1
"Gary''s Student" wrote:
Be sure the data in A1 has a single space before and after the P
--
Gary''s Student - gsnu200778
"Timbo" wrote:
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.
"Timbo" wrote:
Thanks. I'll try that.
"Gary''s Student" wrote:
Very easy. with your data in A1, in B1 enter:
=SUBSTITUTE(A1,"HWC65917 P ","")
--
Gary''s Student - gsnu200778
"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.
Was this post helpful to you?
Reply | Print post TopTop
Gary''s Student 4/11/2008 10:17 AM PST
Try this one:
=RIGHT(A1,LEN(A1)-11)
--
Gary''s Student - gsnu200778
"Timbo" wrote:
It does. It still just duplicates what is in A1
"Gary''s Student" wrote:
Be sure the data in A1 has a single space before and after the P
--
Gary''s Student - gsnu200778
"Timbo" wrote:
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.
"Timbo" wrote:
Thanks. I'll try that.
"Gary''s Student" wrote:
Very easy. with your data in A1, in B1 enter:
=SUBSTITUTE(A1,"HWC65917 P ","")
--
Gary''s Student - gsnu200778
"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.
Did this post answer the question?
Reply | Print post TopTop
Timbo 4/11/2008 11:19 AM PST
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" wrote:
Try this one:
=RIGHT(A1,LEN(A1)-11)
--
Gary''s Student - gsnu200778
"Timbo" wrote:
It does. It still just duplicates what is in A1
"Gary''s Student" wrote:
Be sure the data in A1 has a single space before and after the P
--
Gary''s Student - gsnu200778
"Timbo" wrote:
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.
"Timbo" wrote:
Thanks. I'll try that.
"Gary''s Student" wrote:
Very easy. with your data in A1, in B1 enter:
=SUBSTITUTE(A1,"HWC65917 P ","")
--
Gary''s Student - gsnu200778
"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.
Was this post helpful to you?
Reply | Print post TopTop
Gary''s Student 4/11/2008 3:01 PM PST
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))
--
Gary''s Student - gsnu200778
"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.
Did this post answer the question?
Reply | Print post TopTop
Timbo 4/14/2008 9:22 AM PST
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
"Gary''s Student" wrote:
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))
--
Gary''s Student - gsnu200778
"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.
|