Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data sorting
I have a string of text in Row A Column A (Actually, I have a couple of
hundred rows, but one will suffice as an example) and I want to move parts of that text into other cells on that row. For example: Cell A1 contains The quick brown fox jumped over the lazy dog I want to move "brown fox jumped" to cell A2, leaving the rest in cell A1 I could of course cut & paste with 1 row but not with 200! Obviously if I can achieve this with one row, then I can of course just highlight all the rows and perform the same function. I am sure that I should be able to do this with delimiters but they move everything after "brown fox jumped" as well. How do I set delimiters so that only the word or words that I select are moved to another cell? Any replies much appreciated. Geoff. I |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data sorting
Is there a specific order or position of words which you want to break away
from the string? please provide the details? do you have to pick up the 3rd 4th & 5th word from all the text strings? You may look at breaking the text string into all the different words, and join the wanted words back using concatenate function. Breaking of the string can happen using Data- Text to columns- delimiters option like you mentioned. "Geoff" wrote: I have a string of text in Row A Column A (Actually, I have a couple of hundred rows, but one will suffice as an example) and I want to move parts of that text into other cells on that row. For example: Cell A1 contains The quick brown fox jumped over the lazy dog I want to move "brown fox jumped" to cell A2, leaving the rest in cell A1 I could of course cut & paste with 1 row but not with 200! Obviously if I can achieve this with one row, then I can of course just highlight all the rows and perform the same function. I am sure that I should be able to do this with delimiters but they move everything after "brown fox jumped" as well. How do I set delimiters so that only the word or words that I select are moved to another cell? Any replies much appreciated. Geoff. I |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data sorting
Geoff
Posting one example of the rows of text pretty well eliminates any chance of assistance. How would Excel know which words to extract from each row? Is there any rhyme or reason to the extraction from each row? Words 3, 4 and 5 from each? Gord Dibben MS Excel MVP On Sat, 6 Oct 2007 14:52:45 +0100, "Geoff" wrote: I have a string of text in Row A Column A (Actually, I have a couple of hundred rows, but one will suffice as an example) and I want to move parts of that text into other cells on that row. For example: Cell A1 contains The quick brown fox jumped over the lazy dog I want to move "brown fox jumped" to cell A2, leaving the rest in cell A1 I could of course cut & paste with 1 row but not with 200! Obviously if I can achieve this with one row, then I can of course just highlight all the rows and perform the same function. I am sure that I should be able to do this with delimiters but they move everything after "brown fox jumped" as well. How do I set delimiters so that only the word or words that I select are moved to another cell? Any replies much appreciated. Geoff. I |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data sorting
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... Geoff Posting one example of the rows of text pretty well eliminates any chance of assistance. How would Excel know which words to extract from each row? Is there any rhyme or reason to the extraction from each row? Words 3, 4 and 5 from each? Gord Dibben MS Excel MVP On Sat, 6 Oct 2007 14:52:45 +0100, "Geoff" wrote: I have a string of text in Row A Column A (Actually, I have a couple of hundred rows, but one will suffice as an example) and I want to move parts of that text into other cells on that row. For example: Cell A1 contains The quick brown fox jumped over the lazy dog I want to move "brown fox jumped" to cell A2, leaving the rest in cell A1 I could of course cut & paste with 1 row but not with 200! Obviously if I can achieve this with one row, then I can of course just highlight all the rows and perform the same function. I am sure that I should be able to do this with delimiters but they move everything after "brown fox jumped" as well. How do I set delimiters so that only the word or words that I select are moved to another cell? Any replies much appreciated. Geoff. I My apologies if I didn't make myself clear. I have the following text in the rows of column A A1 Baptismal Reg 10 1908-1948 page: 102 entry no: 815 04 Aug 1929 A2 Baptismal Reg 10 1908-1948 page: 107 entry no: 853 10 Jun 1930 A3 Baptismal Reg 10 1908-1948 page: 130 entry no: 1040 12 Aug 1936 A4 Baptismal Reg 10 1908-1948 page: 131 entry no: 1048 08 Nov 1936 A5 Baptismal Reg 10 1908-1948 page: 137 entry no: 1090 08 Sep 1937 A6 Baptismal Reg 10 1908-1948 page: 167 entry no: 1329 15 Oct 1943 A7 Baptismal Reg 10 1908-1948 page: 17 entry no: 134 19 Mar 1911 A8 Baptismal Reg 10 1908-1948 page: 17 entry no: 135 19 Mar 1911 etc I want to move the "page no" to column B, the "entry no" to column C and the "date" to column D |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data sorting
You can try TTC - Text To Columns.
Select the column of data, and from the menu bar: <Data <TextToColumns Then <Fixed Width <Next Move and / or delete the break lines to separate your data as you wish. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Geoff" wrote in message ... "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Geoff Posting one example of the rows of text pretty well eliminates any chance of assistance. How would Excel know which words to extract from each row? Is there any rhyme or reason to the extraction from each row? Words 3, 4 and 5 from each? Gord Dibben MS Excel MVP On Sat, 6 Oct 2007 14:52:45 +0100, "Geoff" wrote: I have a string of text in Row A Column A (Actually, I have a couple of hundred rows, but one will suffice as an example) and I want to move parts of that text into other cells on that row. For example: Cell A1 contains The quick brown fox jumped over the lazy dog I want to move "brown fox jumped" to cell A2, leaving the rest in cell A1 I could of course cut & paste with 1 row but not with 200! Obviously if I can achieve this with one row, then I can of course just highlight all the rows and perform the same function. I am sure that I should be able to do this with delimiters but they move everything after "brown fox jumped" as well. How do I set delimiters so that only the word or words that I select are moved to another cell? Any replies much appreciated. Geoff. I My apologies if I didn't make myself clear. I have the following text in the rows of column A A1 Baptismal Reg 10 1908-1948 page: 102 entry no: 815 04 Aug 1929 A2 Baptismal Reg 10 1908-1948 page: 107 entry no: 853 10 Jun 1930 A3 Baptismal Reg 10 1908-1948 page: 130 entry no: 1040 12 Aug 1936 A4 Baptismal Reg 10 1908-1948 page: 131 entry no: 1048 08 Nov 1936 A5 Baptismal Reg 10 1908-1948 page: 137 entry no: 1090 08 Sep 1937 A6 Baptismal Reg 10 1908-1948 page: 167 entry no: 1329 15 Oct 1943 A7 Baptismal Reg 10 1908-1948 page: 17 entry no: 134 19 Mar 1911 A8 Baptismal Reg 10 1908-1948 page: 17 entry no: 135 19 Mar 1911 etc I want to move the "page no" to column B, the "entry no" to column C and the "date" to column D |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data sorting
This is not what you originally asked for.
You originally wanted "brown fox jumped" extracted to one cell. Your new sample data is much simpler operation. Use DataText to Columns to break out the three columns. Fixed width will work will work with the data you posted. Pre-select column A then follow the steps in DataText to Columns. Just double-click on the separation lines to remove and double-click where you want a separation line. Gord. On Sat, 6 Oct 2007 18:19:45 +0100, "Geoff" wrote: "Gord Dibben" <gorddibbATshawDOTca wrote in message .. . Geoff Posting one example of the rows of text pretty well eliminates any chance of assistance. How would Excel know which words to extract from each row? Is there any rhyme or reason to the extraction from each row? Words 3, 4 and 5 from each? Gord Dibben MS Excel MVP On Sat, 6 Oct 2007 14:52:45 +0100, "Geoff" wrote: I have a string of text in Row A Column A (Actually, I have a couple of hundred rows, but one will suffice as an example) and I want to move parts of that text into other cells on that row. For example: Cell A1 contains The quick brown fox jumped over the lazy dog I want to move "brown fox jumped" to cell A2, leaving the rest in cell A1 I could of course cut & paste with 1 row but not with 200! Obviously if I can achieve this with one row, then I can of course just highlight all the rows and perform the same function. I am sure that I should be able to do this with delimiters but they move everything after "brown fox jumped" as well. How do I set delimiters so that only the word or words that I select are moved to another cell? Any replies much appreciated. Geoff. I My apologies if I didn't make myself clear. I have the following text in the rows of column A A1 Baptismal Reg 10 1908-1948 page: 102 entry no: 815 04 Aug 1929 A2 Baptismal Reg 10 1908-1948 page: 107 entry no: 853 10 Jun 1930 A3 Baptismal Reg 10 1908-1948 page: 130 entry no: 1040 12 Aug 1936 A4 Baptismal Reg 10 1908-1948 page: 131 entry no: 1048 08 Nov 1936 A5 Baptismal Reg 10 1908-1948 page: 137 entry no: 1090 08 Sep 1937 A6 Baptismal Reg 10 1908-1948 page: 167 entry no: 1329 15 Oct 1943 A7 Baptismal Reg 10 1908-1948 page: 17 entry no: 134 19 Mar 1911 A8 Baptismal Reg 10 1908-1948 page: 17 entry no: 135 19 Mar 1911 etc I want to move the "page no" to column B, the "entry no" to column C and the "date" to column D |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data sorting
Many thanks! That's exactly what I needed.
Geoff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting data | Excel Worksheet Functions | |||
Sorting Data | Excel Discussion (Misc queries) | |||
Sorting data to match existing data | Excel Discussion (Misc queries) | |||
Sorting Data | New Users to Excel | |||
colors of bar charted data don't follow data after sorting | Charts and Charting in Excel |