View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.misc
Ian Grega Ian Grega is offline
external usenet poster
 
Posts: 39
Default 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