|
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. |
Continuation: Formula for Duplicating info from one cell to anothe
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. -- Kevin Backmann "Timbo" wrote: 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. |
Continuation: Formula for Duplicating info from one cell to an
Thanks, but not quite, though.
I have two types of strings he 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. "Kevin B" wrote: 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. -- Kevin Backmann "Timbo" wrote: 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: |
Continuation: Formula for Duplicating info from one cell to anothe
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 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. |
Continuation: Formula for Duplicating info from one cell to an
How about this one, which checks for the <spaceP<space:
=IF(MID(A8,FIND(" ",A8,1),3)=" P ",RIGHT(A8,LEN(A8)-(FIND(" ",A7,1)+2)),RIGHT(A8,LEN(A8)-FIND(" ",A8,1))) -- Kevin Backmann "Timbo" wrote: Thanks, but not quite, though. I have two types of strings he 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. "Kevin B" wrote: 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. -- Kevin Backmann "Timbo" wrote: 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. |
Continuation: Formula for Duplicating info from one cell to an
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 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: |
Continuation: Formula for Duplicating info from one cell to an
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) "Timbo" wrote: 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 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 / |
Continuation: Formula for Duplicating info from one cell to an
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 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: |
Continuation: Formula for Duplicating info from one cell to an
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 info. "Timbo" wrote: Thanks. I'll try that. |
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 |
| All times are GMT +1. The time now is 05:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com