Continuation: Formula for Duplicating info from one cell to anothe

[Timbo]

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

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.

Very easy. with your data in A1, in B1 enter:

=SUBSTITUTE(A1,"HWC65917 P ","")

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.

Thanks. I'll try that.

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

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.

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

Be sure the data in A1 has a single space before and after the P

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

It does. It still just duplicates what is in A1

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.

Try this one:

=RIGHT(A1,LEN(A1)-11)

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

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

 

[Kevin B]

If all the entries start with a catalogue number and are then followed by a
description, the following formula might work for you:

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

As long as the first occurrence of a space in the entry follows the
catalogue number it will work.

 

[Timbo]

Thanks, but not quite, though.

I have two types of strings here: One with a P between the number and the
word Science, the other does not. Your formula works for the ones that don't.
On the ones that do have a P, the P shows up first. What I want is for
everything after the P and the space after to be deleted.

This one works, and has no P between the alphanumeric code and word Science:

HWC9322 Science / Life Sciences (Biology) / Medical Sciences / Anatomy &
Physiology / Human Systems / Reproductive System

with your formula comes out correctly as

Science / Life Sciences (Biology) / Medical Sciences / Anatomy & Physiology
/ Human Systems / Reproductive System

However, when a string does have a P, the P shows up before the word
Science, which is what I don't want:

HWC9572 P Science / Life Sciences (Biology) / Medical Sciences / Anatomy &
Physiology / Human Systems / Reference / Atlases & Images

comes out as

P Science / Life Sciences (Biology) / Medical Sciences / Anatomy &
Physiology / Human Systems / Reference / Atlases & Images

What I want the word Science to show up first, regardless of the type of
string.

Hope this isn't too confusing.

Thanks for the help.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Kevin B]

How about this one, which checks for the <space>P<space>:

=IF(MID(A8,FIND(" ",A8,1),3)=" P ",RIGHT(A8,LEN(A8)-(FIND("
",A7,1)+2)),RIGHT(A8,LEN(A8)-FIND(" ",A8,1)))

 

[Timbo]

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.

 

[Ian Grega]

If the word Science is allways in the text then the following formula will
give everything after and including the word "Science"

=MID(A1,FIND("Science",A1),250)

 

[Rick Rothstein \(MVP - VB\)]

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]

Ian,

You have broken through!! And the formula was so simple, too. Thank you very
much.

 

[Ian Grega]

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