| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
USE PART OF TEXT FROM ONE CELL IN ANOTHER
I have the following in one cell NAME fred /bloggs/ then down to cells
with other info in them before another cell with NAME freda /bloggs/ etc I have discovered how to check for the existance of "NAME" in a cell but how can I copy the "fred" and "bloggs" to other cells when the "NAME" exists? Is it also possible to check the next cell down if "NAME" doesn't appear in a cell and carry on to the next other cell down so that I get fred bloggs freda bloggs etc ? -- Thanks Bob |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
USE PART OF TEXT FROM ONE CELL IN ANOTHER
If your data is in A1:A100 then in C1 try the formula
=IF(COUNTIF(A$1:A$100,"NAME*")ROW()-ROW(C$1),INDEX(A$1:A$100,SMALL(IF(ISNUMBER(FIND("N AME ",A$1:A$100)),ROW(A$1:A$100)-ROW(C$1)+1,""),ROW()-ROW(C$1)+1)),"") confirmed with CTRL+SHIFT+ENTER and copied down the column "Bob" wrote: I have the following in one cell NAME fred /bloggs/ then down to cells with other info in them before another cell with NAME freda /bloggs/ etc I have discovered how to check for the existance of "NAME" in a cell but how can I copy the "fred" and "bloggs" to other cells when the "NAME" exists? Is it also possible to check the next cell down if "NAME" doesn't appear in a cell and carry on to the next other cell down so that I get fred bloggs freda bloggs etc ? -- Thanks Bob |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
USE PART OF TEXT FROM ONE CELL IN ANOTHER
to daddylonglegs, thanks for your reply I have been trying various ways of
implementing it , it shows up on the discussion site in four rows, I have added (or replaced the space after the last word (NAME ) with) at the end of row two with the * symbol. This lets excel allow it as a formulae. I have placed it in C1 and confirmed it with CTRL-SHIFT-ENTER ( I think, is that press all three instead of just ENTER? -does this do something different to just enter?) and all I get is #NUM. I have copied it down the column but notice the cell references don't change. I only get a #NUM in the first nine cells ,even though I copied it further and there is at least one true cell in column A (row 4). I failed to find the answer, do you see one? My list is in A1 to A230. with a "NAME **** /****/" about every 10 but it does differ slightly. Thanks Bob "daddylonglegs" wrote: If your data is in A1:A100 then in C1 try the formula =IF(COUNTIF(A$1:A$100,"NAME*")ROW()-ROW(C$1),INDEX(A$1:A$100,SMALL(IF(ISNUMBER(FIND("N AME ",A$1:A$100)),ROW(A$1:A$100)-ROW(C$1)+1,""),ROW()-ROW(C$1)+1)),"") confirmed with CTRL+SHIFT+ENTER and copied down the column "Bob" wrote: I have the following in one cell NAME fred /bloggs/ then down to cells with other info in them before another cell with NAME freda /bloggs/ etc I have discovered how to check for the existance of "NAME" in a cell but how can I copy the "fred" and "bloggs" to other cells when the "NAME" exists? Is it also possible to check the next cell down if "NAME" doesn't appear in a cell and carry on to the next other cell down so that I get fred bloggs freda bloggs etc ? -- Thanks Bob |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
USE PART OF TEXT FROM ONE CELL IN ANOTHER
I am still playing with this and have found a partial solution. The '*' I put
in caused a problem, find doesn't allow wild cards! without it the formulae returns the names I want or almost, what it does do is give me NAME fred bloggs NAME freda bloggs etc I have only nine lines because that is the total number of NAME's in the list, I'm sure I can extend this to include all the list. All I need to do now is be able to seperate the three words into drop the word NAME and put the second word into F1 to F(number of names) third word into G1 to G(number of names). can you help please? -- Thanks Bob "Bob" wrote: to daddylonglegs, thanks for your reply I have been trying various ways of implementing it , it shows up on the discussion site in four rows, I have added (or replaced the space after the last word (NAME ) with) at the end of row two with the * symbol. This lets excel allow it as a formulae. I have placed it in C1 and confirmed it with CTRL-SHIFT-ENTER ( I think, is that press all three instead of just ENTER? -does this do something different to just enter?) and all I get is #NUM. I have copied it down the column but notice the cell references don't change. I only get a #NUM in the first nine cells ,even though I copied it further and there is at least one true cell in column A (row 4). I failed to find the answer, do you see one? My list is in A1 to A230. with a "NAME **** /****/" about every 10 but it does differ slightly. Thanks Bob "daddylonglegs" wrote: If your data is in A1:A100 then in C1 try the formula =IF(COUNTIF(A$1:A$100,"NAME*")ROW()-ROW(C$1),INDEX(A$1:A$100,SMALL(IF(ISNUMBER(FIND("N AME ",A$1:A$100)),ROW(A$1:A$100)-ROW(C$1)+1,""),ROW()-ROW(C$1)+1)),"") confirmed with CTRL+SHIFT+ENTER and copied down the column "Bob" wrote: I have the following in one cell NAME fred /bloggs/ then down to cells with other info in them before another cell with NAME freda /bloggs/ etc I have discovered how to check for the existance of "NAME" in a cell but how can I copy the "fred" and "bloggs" to other cells when the "NAME" exists? Is it also possible to check the next cell down if "NAME" doesn't appear in a cell and carry on to the next other cell down so that I get fred bloggs freda bloggs etc ? -- Thanks Bob |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
USE PART OF TEXT FROM ONE CELL IN ANOTHER
As a seperate formulae I have been able to seperate the first and the last
words but I am having trouble identifying the count of the letters in the middle word, I don't seem to be able to get to grips with the mid function. I'm sure you will be able to put it all into one formulae at least I hope so, please help. -- Thanks Bob "Bob" wrote: I am still playing with this and have found a partial solution. The '*' I put in caused a problem, find doesn't allow wild cards! without it the formulae returns the names I want or almost, what it does do is give me NAME fred bloggs NAME freda bloggs etc I have only nine lines because that is the total number of NAME's in the list, I'm sure I can extend this to include all the list. All I need to do now is be able to seperate the three words into drop the word NAME and put the second word into F1 to F(number of names) third word into G1 to G(number of names). can you help please? -- Thanks Bob "Bob" wrote: to daddylonglegs, thanks for your reply I have been trying various ways of implementing it , it shows up on the discussion site in four rows, I have added (or replaced the space after the last word (NAME ) with) at the end of row two with the * symbol. This lets excel allow it as a formulae. I have placed it in C1 and confirmed it with CTRL-SHIFT-ENTER ( I think, is that press all three instead of just ENTER? -does this do something different to just enter?) and all I get is #NUM. I have copied it down the column but notice the cell references don't change. I only get a #NUM in the first nine cells ,even though I copied it further and there is at least one true cell in column A (row 4). I failed to find the answer, do you see one? My list is in A1 to A230. with a "NAME **** /****/" about every 10 but it does differ slightly. Thanks Bob "daddylonglegs" wrote: If your data is in A1:A100 then in C1 try the formula =IF(COUNTIF(A$1:A$100,"NAME*")ROW()-ROW(C$1),INDEX(A$1:A$100,SMALL(IF(ISNUMBER(FIND("N AME ",A$1:A$100)),ROW(A$1:A$100)-ROW(C$1)+1,""),ROW()-ROW(C$1)+1)),"") confirmed with CTRL+SHIFT+ENTER and copied down the column "Bob" wrote: I have the following in one cell NAME fred /bloggs/ then down to cells with other info in them before another cell with NAME freda /bloggs/ etc I have discovered how to check for the existance of "NAME" in a cell but how can I copy the "fred" and "bloggs" to other cells when the "NAME" exists? Is it also possible to check the next cell down if "NAME" doesn't appear in a cell and carry on to the next other cell down so that I get fred bloggs freda bloggs etc ? -- Thanks Bob |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to convert a month to a quarter ...... | New Users to Excel | |||
| Extracting part of Text from one cell to another | Excel Worksheet Functions | |||
| removing part of text string to another cell | Excel Discussion (Misc queries) | |||
| Code to get text from part of a cell | Excel Worksheet Functions | |||
| Shade cell according to text? | Excel Discussion (Misc queries) | |||
Hybrid Mode
