Posted to microsoft.public.excel.misc
|
|
Continuation: Formula for Duplicating info from one cell to an
Timbo,
My pleasure, I received some great assistance with a macro on this forum and
was intent on reciprocating the favour, albeit as a non expert. And would you
believe it I cracked it on the first go. I better give up now as I would
probably end up sending someone up the wrong path.
The MVP's and other regular contributors on this forum are fantastic with
the assistance they provide.
Ian Grega
"Timbo" wrote:
Ian,
You have broken through!! And the formula was so simple, too. Thank you very
much.
"Rick Rothstein (MVP - VB)" wrote:
As long as the letter that may or may not be there is always a single P
surrounded by spaces, give this a try...
=MID(SUBSTITUTE(A19,"P ",""),1+FIND(" ",SUBSTITUTE(A19,"P ","")),255)
Rick
"Timbo" wrote in message
...
Rick,
No, it's not always the same length. The alphanumeric code can have
anywhere
from one to five digits. Some have Ps, others don't. Here are some
examples:
HWC9572 P Science / Life Sciences (Biology) / Medical Sciences / Anatomy &
Physiology / Human Systems / Reference / Atlases & Images
HWC16702 P Science / Life Sciences (Biology) / Medical Sciences / Anatomy
&
Physiology / Human Systems / Reference / Tutorials
HWC9322 Science / Life Sciences (Biology) / Medical Sciences / Anatomy &
Physiology / Human Systems / Reproductive System
HWC504 P Science / Life Sciences (Biology) / Medical Sciences / Anatomy &
Physiology / Human Systems / Reproductive System / Diseases & Disorders /
Breast Cancer
Your formula didn't work for HWC9322 because it doesn't have the P between
the alphanumeric code and the word Science. It did work for all the
others,
however.
Kevin,
I'm not even sure how to apply your formula. All I want is for a formula
in
Column A to be translated into Column B.
"Rick Rothstein (MVP - VB)" wrote:
We need to know more about the "HWC65917 P " part. Is it always the same
length? If so...
=MID(A1,12,999) <<or =RIGHT(A1,LEN(A1)-11)
If not, are there always 2 blank spaces before the word (Science in this
case) or words that you want to preserve? If so...
=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,999)
If not, something else?
Rick
"Timbo" wrote in message
...
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
|