Data sorting

  • Thread starter Thread starter Geoff
  • Start date Start date
G

Geoff

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
 
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

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
 
Gord Dibben said:
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
Click to expand...

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
 
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!
=====================================================


Gord Dibben said:
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
Click to expand...

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
 
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 Data>Text 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 Data>Text to Columns.

Just double-click on the separation lines to remove and double-click where you
want a separation line.


Gord.
 
Back
Top